MariaDB/MySQL

MariaDB is an open source fork of MySQL. There are some differences but most are internal, for users the commands and SQL syntax is exactly the same.

phpMyAdmin is a web based GUI for the maintenance of MariaDB and MySQL servers.

Move users from one server to another

Starting on the source server execute this script

PASSWORD=$1
mysql -B -N -uroot -p${PASSWORD} -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user IN ('username1', 'username2', 'username3')" > users.txt
while read line
 do
  mysql -B -N -uroot -p${PASSWORD} -e "SHOW GRANTS FOR $line"
 done < users.txt > users.sql
sed -i 's/$/;/' users.sql

Move the file to the other server and there do:

mysql -u root -p < users.sql

Set up replication

With replication all transactions on the master database server are immediately replicated to the slave database server. So you create a hot copy of the database that can be used in case of a failure of the master server or to offload the master server by executing retrieval queries on the slave.

This procedure is for a large part based on an tutorialfound on howtoforge.com [1]

On the master

Edit the my.cnf so it has below lines.

The server must be accessible from the slave server and the server-id must be unique in you network

The binlog-do-db specifies for which database the binlog is written, one line per database. If omitted for all databases binlog is written which is the easiest setup.

#skip-networking
#bind-address            = 127.0.0.1
log-bin = /var/log/mysql/mysql-bin.log
#binlog-do-db=exampledb
server-id=1

Restart the database server

/etc/init.d/mysql restart

Create a replication user

mysql -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';
mysql> FLUSH PRIVILEGES;

mysql> USE exampledb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Dump the database

mysqldump -u root -p<password> --opt exampledb > exampledb.sql

Unlock the tables

mysql -u root -p<password>
mysql>UNLOCK TABLES;
mysql> quit;

On the slave server

Create the slave database

mysql -u root -p
mysql> CREATE DATABASE exampledb;
mysql> quit;

Load the database dump created on the master

mysql -u root -p<password> exampledb < /path/to/exampledb.sql

Edit the my.cnf so it has these lines

The server-id must be unique in you network

The replicate-do-db specifies for which database the binlog on the master is read, one line per database. If omitted for all databases binlog is read.

server-id=2
replicate-do-db=exampledb

Restart the database server

/etc/init.d/mysql restart

In the following statement the values from SHOW MASTER STATUS above must be used.

mysql -u root -p<password>
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST=<masterIP>, MASTER_USER='slave_user', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183; 
mysql> START SLAVE;
mysql> quit;

Repair replication

On the slave server

Stop replication processes

mysql -u root -p
mysql> stop slave;

On the master server

Dump the replicated databases including the replication data. mysqldump --databases <db1> <db2> --master-data=1 > ../mysql_dump_$(hostname)_$(date +%Y-%m-%d_%H-%M).sql.dump

On the slave server

Import the databases

mysql -uroot -p < <mysql_dump_...sql.dump

Restart the slave processes and check the slave status.

mysql -u root -p
mysql> start slave;
mysql> show slave status \G

Populate and update timezone information

Timezone information is required to use functions like CONVERT_TZ. The mysql.time_zone table need to be populated and maintained when the data changes.

On a synology server following command takes care of this:

/volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p mysql