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.
- show status like 'Qcache_total_blocks'
- Fragmentation of the query cache (lower is better)
- flush query cache
- Flush the query cache to fix the fragmentation (check if cache sizes need to be changed too)
User management
- alter user username@host identified by 'password'
- Change a users password
- drop user username@host
- Delete a user
- grans all privileges on *.* to 'user'@'host';
- Grant all privileges to all database to a user
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.
The slave server logs into the master and checks if the bin-log position differs, if so all transactions after the last binlog-position are redone on the slave.
This procedure is for a large part based on a tutorial found on howtoforge.com [1]
For multi master replication to 1 slave check this.
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 except those specified by binlog-ingnore-db.
#skip-networking #bind-address = 127.0.0.1 log-bin = /var/log/mysql/mysql-bin.log #binlog-do-db=exampledb binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = phpmyadmin 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;
- Make sure nothing is changes in the database during backup
mysql> USE exampledb; mysql> FLUSH TABLES WITH READ LOCK;
Or, for all databases;
mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = 1;
- Check status;
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;
Or, for all databases;
mysql> SET GLOBAL read_only = 0; mysql> UNLOCK TABLES;
On the slave server
- Create the slave database
mysql -u root -p<password>
CREATE DATABASE exampledb;
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>
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST=<masterIP>, MASTER_USER='slave_user', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;
START SLAVE;
quit;
In case of using GTID for replication
mysql -u root -p<password>
CHANGE MASTER TO master_host= <masterIP>', master_user='<slave_user>', MASTER_PASSWORD='<password>', master_use_gtid=current_pos;
SELECT @@GLOBAL.gtid_slave_pos;
- Show current GTID position
SET GLOBAL gtid_slave_pos = ""
- Reset counter, slave databases must be in sync with the master.
Add a database to replication
Everything must be working fine and log positions up to date. On the slave Read_Master_Log_Pos is the same as the Exec_Master_Log_Pos and the Read_Master_Log_Pos is the same as the masters Position (in show master status).
Then you can do:
- Slave: Add database to my.cnf (replicate-do-db=<dbname>)
- Slave:
stop slave;
- Master:
flush tables with read lock;
- Master: dump database
- Master:
unlock tables
- Slave: Load the database dump
- Slave: Restart the database server
Then check the slave status show slave status \G
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 < <dumpfile>
- Restart the slave processes and check the slave status.
When needed update the slave log-file and position as in the masters show slave status
mysql> CHANGE MASTER TO MASTER_LOG_FILE='<filename>', MASTER_LOG_POS=<postition>;
Then:
mysql> start slave; mysql> show slave status \G
This should not show any errors
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