Difference between revisions of "MariaDB/MySQL"
(Created page with "[https://mariadb.org/ MariaDB] is an open source fork of [http://www.mysql.com/ MySQL]. There are some differences but most are internal, for users the commands and SQL syntax...") |
m |
||
(9 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | [https://mariadb.org/ MariaDB] is an open source fork of [http://www.mysql.com/ MySQL]. There are some differences but most are internal, for users the commands and SQL syntax is exactly the same. | + | [https://mariadb.org/ MariaDB] is an open source fork of [http://www.mysql.com/ MySQL]. There are some differences but most are internal, for users the commands and [[SQL]] syntax is exactly the same. |
[https://www.phpmyadmin.net/ phpMyAdmin] is a web based GUI for the maintenance of MariaDB and MySQL servers. | [https://www.phpmyadmin.net/ phpMyAdmin] is a web based GUI for the maintenance of MariaDB and MySQL servers. | ||
Line 5: | Line 5: | ||
=Move users from one server to another= | =Move users from one server to another= | ||
− | Starting on the source server execute | + | Starting on the source server execute this script |
<syntaxhighlight lang=bash> | <syntaxhighlight lang=bash> | ||
− | mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user IN ('username1', 'username2', 'username3')" > users.txt | + | 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 | while read line | ||
do | do | ||
− | mysql -B -N -uroot -p -e "SHOW GRANTS FOR $line" | + | mysql -B -N -uroot -p${PASSWORD} -e "SHOW GRANTS FOR $line" |
done < users.txt > users.sql | done < users.txt > users.sql | ||
sed -i 's/$/;/' users.sql | sed -i 's/$/;/' users.sql | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | Move the file to the other server and there do: | ||
+ | |||
+ | <code>mysql -u root -p < users.sql</code> | ||
+ | |||
+ | =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 a found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication] | ||
+ | |||
+ | ==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. | ||
+ | #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 | ||
− | mysql -u root -p < | + | ;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 | exampledb | | | ||
+ | +---------------+----------+--------------+------------------+ | ||
+ | 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; | ||
+ | |||
+ | =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: | ||
+ | |||
+ | <code>/volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p mysql</code> |
Revision as of 13:22, 5 July 2020
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 a found 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.
#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 | exampledb | | +---------------+----------+--------------+------------------+ 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;
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