Difference between revisions of "MariaDB/MySQL"

From wiki
Jump to: navigation, search
(Set up replication)
Line 25: Line 25:
 
This procedure is a found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication]
 
This procedure is a found on howtoforge.com [https://www.howtoforge.com/mysql_database_replication]
  
On the master:
+
==On the master:==
 
;Edit the my.cnf so it has these lines
 
;Edit the my.cnf so it has these lines
 
  #skip-networking
 
  #skip-networking
Line 51: Line 51:
 
  1 row in set (0.00 sec)
 
  1 row in set (0.00 sec)
  
To be continued.....
+
;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
 +
server-id=2
 +
master-host=<masterIP>
 +
master-user=slave_user
 +
master-password=<password>
 +
master-connect-retry=60
 +
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> SLAVE STOP;
 +
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> SLAVE START;
 +
mysql> quit;

Revision as of 18:44, 20 October 2019

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 these lines
#skip-networking
#bind-address            = 127.0.0.1
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1

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

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
server-id=2
master-host=<masterIP>
master-user=slave_user
master-password=<password>
master-connect-retry=60
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> SLAVE STOP;
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> SLAVE START;
mysql> quit;