mysql_secure_installation: command not found

It’s always a good idea to do basic MySQL security measures after installing fresh version of MariaDB server. For this task, I always used “mysql_secure_installation” command, which was part of installation. Some time ago, I’ve got stucked when trying to do so on MariaDB version 10.7. After some searching around, I guess command was changed.

[root@server ~]# mysql_secure_installation
-bash: mysql_secure_installation: command not found

This is a correct way on newer MariaDB versions:

root@server ~]# mariadb-secure-installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
. . .

 

Migrate all databases to remote server with mysqldump in one step – 1:1 migration

Here is quick one, last in this year :). So I had to move a lot of databases to another server, but problem was, that on source server there wasn’t enough disk available. Also it was migration from very old mysql version to mariadb so mysqldump is your friend. Mysqldump all databases was out of the question because of low disk space. Dumping each database on its own would take too long and too many effort. But you can create dump of database and import it on new server in the same step.

First, you’ll need list of all databases on your source server and create them on new server. If your mysqldump creates “create database”, then you don’t need to create them manually on new server. If you want, you can skip mysql and any other databases that you don’t wish to transfer with grep. Put list of databases in some file – databases.txt for example. But first, make shure that command bellow show all databases. It is also necessary that you can remote access to mysql from source server to new server.

Test list all databases (exclude unwanted ones):

[root@oldserver ~]# mysql -e 'show databases' | grep -v "|" | grep -v "Database\|information_schema\|mysql\|performance_schema"
database1
geekytuts
database2
database3
database4
database5

Then put list of databases in text file databasest.txt:

[root@oldserver ~]# mysql -e 'show databases' | grep -v "|" | grep -v "Database\|information_schema\|mysql\|performance_schema" > databases.txt

Then you can import database to remote server like this:

mysqldump -u root -ppassword --single-transaction --skip-lock-tables database1 | mysql -h 1.1.1.1 -u root -ppassword database1

If you want to import all/multi databases, then use database.txt that we created in first step with for loop:

for i in `cat databases.txt`; do mysqldump -u root -ppassword --single-transaction --skip-lock-tables $i | mysql -h 1.1.1.1 -u root -ppassword $i; done

Bonus: If you need to create all databases listed in databases.txt on new server manualy, then you can also create all of them in one step. Use databases.txt on new server. If your mysqldump creates “create database” also, then you can skip this step.

[root@newserver ~]# for i in `cat databases.txt`; do mysql -u root -ppassword -e create database $i; done

Hope this helps someone.

Happy new year!

Moodle – mysql/mariadb Antelope to Barracuda installation warning

I tried to install Moodle 3.5.2 on cPanel server and got this warning in installation process:

“Your database has tables using Antelope as the file format. You are recommended to convert the tables to the Barracuda file format. See the documentation Administration via command line for details of a tool for converting InnoDB tables to Barracuda.”

Because this was production hosting server, I was unable to just globally change mysql settings. To be able to proceed installation, open config.php file of your moodle and change variable dbcollation like this:

dbcollation' => 'utf8_unicode_ci'

[kofi]

MySQL/MariaDB – [ERROR] Plugin InnoDB registration as a STORAGE ENGINE failed error

I was migrating server and rsync all databases to new mariadb server. When tried to start mariadb on new server, I was getting this error:

Apr 24 18:30:26 my.server.com mysqld[9703]: 2018-04-24 18:30:26 140298644924544 [ERROR] Plugin 'InnoDB' init function returned error.
Apr 24 18:30:26 my.server.com mysqld[9703]: 2018-04-24 18:30:26 140298644924544 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Apr 24 18:30:26 my.server.com mysqld[9703]: 2018-04-24 18:30:26 140298644924544 [Note] Plugin 'FEEDBACK' is disabled.
Apr 24 18:30:26 my.server.com mysqld[9703]: 2018-04-24 18:30:26 140298644924544 [ERROR] Unknown/unsupported storage engine: InnoDB
Apr 24 18:30:26 my.server.com mysqld[9703]: 2018-04-24 18:30:26 140298644924544 [ERROR] Aborting

Solution is to generate new ib_logfile0 and ib_logfile1 files. Just try steps bellow.

[root@lol ~]# cd /var/lib/mysql
[root@lol mysql]# mv ib_logfile0 ib_logfile0-backup 
[root@lol mysql]# mv ib_logfile1 ib_logfile1-backup
[root@lol mysql]# systemctl start mysql
[root@lol mysql]# mysql
Welcome to the MariaDB monitor.

It should work.

[kofi]

How to save mysql query output into a file

Sometimes you may want to save output of some mysql query to a text file. Maybe even to Excel’s spreadsheet file so that you have more control with editing, sorting … MySQL offers many useful options there.

Below is an example on how to save some mysql query output to csv file. You can terminate fields with some key character which is super useful. This example has fields terminated with ; and lines with \n.

mysql> select firstname, lastname, email, phone from clients INTO OUTFILE '/tmp/outputfile.csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';

Just make sure that mysql has suitable permissions so that it will be able to write to a file – chmod 777.

How to change administrator username in WordPress

By default, WordPress won’t allow you to change username of  your administrator account. There are several ways to do this. There are even plugins for this, but I think using plugins for this task is unnecessary and bad idea in general. WordPress is great but consider using as less plugins as you can. Especially bad ones, they are just calling to be hacked by evil guys with too much time. 🙂

Here is how to change administrators username with one simple mysql command.

First, select your wordpress database.

mysql> show tables;
+-----------------------+
| Tables_in_sample-blog |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_snippets           |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+

So in this case I want to change username for my admin user.

mysql> select id, user_login from wp_users;
+----+------------+
| id | user_login |
+----+------------+
|  1 | admin      |
|  2 | someuser   |
+----+------------+

You just have to update user_login field in wp_users table with command below. Of course change id and user_login value to your needs.

mysql> update wp_users set user_login="igor" where id="1";
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Username is now changed. You can login in your wordpress with new username.

mysql> select id, user_login from wp_users;
+----+------------+
| id | user_login |
+----+------------+
|  1 | igor       |
|  2 | someuser   |
+----+------------+

Create dump of specific tables from mysql database

You can simply create backups of specific tables with mysqldump.

mysqldump -u  -p  databasename table1 table2 table3 ... > mysqldump_file.sql

© 2024 geegkytuts.net
Hosted by SIEL


About author