Difference between revisions of "MariaDB/MySQL"
(4 intermediate revisions by the same user not shown) | |||
Line 40: | Line 40: | ||
This procedure is for a large part based on a tutorial found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication] | This procedure is for a large part based on a tutorial found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication] | ||
+ | |||
+ | For multi master replication to 1 slave check [https://mariadb.com/kb/en/multi-source-replication/ this]. | ||
==On the master== | ==On the master== | ||
Line 63: | Line 65: | ||
mysql> FLUSH PRIVILEGES; | mysql> FLUSH PRIVILEGES; | ||
+ | ;Make sure nothing is changes in the database during backup | ||
mysql> USE exampledb; | mysql> USE exampledb; | ||
mysql> FLUSH TABLES WITH READ LOCK; | 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; | mysql> SHOW MASTER STATUS; | ||
+---------------+----------+--------------+------------------+ | +---------------+----------+--------------+------------------+ | ||
Line 78: | Line 85: | ||
;Unlock the tables | ;Unlock the tables | ||
mysql -u root -p<password> | mysql -u root -p<password> | ||
− | mysql>UNLOCK TABLES; | + | mysql> UNLOCK TABLES; |
mysql> quit; | mysql> quit; | ||
+ | Or, for all databases; | ||
+ | mysql> SET GLOBAL read_only = 0; | ||
+ | mysql> UNLOCK TABLES; | ||
==On the slave server== | ==On the slave server== | ||
;Create the slave database | ;Create the slave database | ||
− | + | <code>mysql -u root -p<password></code> | |
− | + | <syntaxhighlight lang=sql> | |
− | + | CREATE DATABASE exampledb; | |
+ | quit; | ||
+ | </syntaxhighlight> | ||
;Load the database dump created on the master | ;Load the database dump created on the master | ||
− | + | <code>mysql -u root -p<password> exampledb < /path/to/exampledb.sql</code> | |
;Edit the my.cnf so it has these lines | ;Edit the my.cnf so it has these lines | ||
:The server-id must be unique in you network | :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. | :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. | ||
− | + | <syntaxhighlight lang=text> | |
− | + | server-id=2 | |
+ | replicate-do-db=exampledb | ||
+ | </syntaxhighlight> | ||
;Restart the database server | ;Restart the database server | ||
− | + | <code>/etc/init.d/mysql restart</code> | |
In the following statement the values from SHOW MASTER STATUS above must be used. | In the following statement the values from SHOW MASTER STATUS above must be used. | ||
− | + | ||
− | + | <code>mysql -u root -p<password></code> | |
− | + | <syntaxhighlight lang=sql> | |
− | + | 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; | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | ====In case of using GTID for replication==== | ||
+ | <code>mysql -u root -p<password></code> | ||
+ | <syntaxhighlight lang=sql> | ||
+ | CHANGE MASTER TO master_host= <masterIP>', master_user='<slave_user>', MASTER_PASSWORD='<password>', master_use_gtid=current_pos; | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | ;<code>SELECT @@GLOBAL.gtid_slave_pos;</code> | ||
+ | :Show current GTID position | ||
+ | ;<code>SET GLOBAL gtid_slave_pos = ""</code> | ||
+ | :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: <code>stop slave;</code> | ||
+ | * Master: <code>flush tables with read lock;</code> | ||
+ | * Master: dump database | ||
+ | * Master: <code>unlock tables</code> | ||
+ | * Slave: Load the database dump | ||
+ | * Slave: Restart the database server | ||
+ | Then check the slave status <code>show slave status \G</code> | ||
=Repair replication= | =Repair replication= | ||
Line 119: | Line 161: | ||
==On the slave server== | ==On the slave server== | ||
;Import the databases | ;Import the databases | ||
− | <code>mysql -uroot -p < < | + | <code>mysql -uroot -p < <dumpfile></code> |
;Restart the slave processes and check the slave status. | ;Restart the slave processes and check the slave status. | ||
− | mysql | + | When needed update the slave log-file and position as in the masters <code>show slave status</code> |
+ | mysql> CHANGE MASTER TO MASTER_LOG_FILE='<filename>', MASTER_LOG_POS=<postition>; | ||
+ | Then: | ||
mysql> start slave; | mysql> start slave; | ||
mysql> show slave status \G | mysql> show slave status \G | ||
+ | This should not show any errors | ||
=Populate and update timezone information= | =Populate and update timezone information= |
Latest revision as of 18:53, 20 January 2023
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