On the test, I will configure the replication in MySQL.
Suppose there are two servers with Ubuntu Server installed, on both of them we will install MySQL server and client, if they are not installed:
sudo apt-get install mysql-server mysql-client
I also described the MySQL server installation in the article:
Installing and configuring a MySQL server on Ubuntu.
Suppose that the main MySQL Master server has an IP address of 192.168.1.8, the second is 192.168.1.9, we will configure it as a Slave.
On the master server, open the MySQL configuration file in the text editor:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Let’s specify which IP the server is running, assign the ID to it, specify the path to bin logs, and from which database they will be written:
bind-address = 192.168.1.8
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = TESTDATABASE
max_binlog_size = 500M
You can specify several databases:
binlog_do_db = TESTDATABASE
binlog_do_db = TESTDATABASE2
binlog_do_db = TESTDATABASE3
In bin logs, queries that are executed to the specified database on the Master server are saved and later on they will be executed on Slave servers, thus bringing the database to the current state, if desired, you can see them through the mysqlbinlog utility.
Restart the MySQL server to apply the changes:
sudo service mysql restart
Create a user who will connect from the Slave server and receive the data:
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";
FLUSH PRIVILEGES;
exit
If there are a lot of Slave servers, then we will add similarly users specifying the necessary IP instead of 192.168.1.9, or we will specify the symbol * to allow the user to access from any IP.
In order to do a dump, we will block all tables in the database:
mysql -u root -p
USE TESTDATABASE;
FLUSH TABLES WITH READ LOCK;
We look at the status of the Master Server:
SHOW MASTER STATUS;
exit
When I looked at the status of the master of the north, I saw the following:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000040 | 154 | TESTDATABASE | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0,00 sec)
Since we have locked the tables in the database and the data does not change, and mysql-bin.000040 and 154 will be useful in the future to specify at what point to start the transfer of data.
Now we are dumping the database:
mysqldump -u root -p --opt TESTDATABASE > TESTDATABASE.sql
Now you can unlock the tables:
mysql -u root -p
UNLOCK TABLES;
After unblocking the tables, if the database on the Master server changes, the Position value will increase.
We proceed to configure the Slave server, create a database:
mysql -u root -p
CREATE DATABASE TESTDATABASE;
exit
We import the previously made dump into it:
mysql -u root -p TESTDATABASE < TESTDATABASE.sql
Open the configuration file of the MySQL server:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Specify the parameters:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = TESTDATABASE
max_binlog_size = 500M
Restart the MySQL server to apply the changes:
sudo service mysql restart
Run a query that specifies the options for replication, including File and Position, which we saw on the master server before we dump the database with locked tables, thereby indicating from which position to start replication:
CHANGE MASTER TO MASTER_HOST='192.168.1.8', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE = 'mysql-bin.000040', MASTER_LOG_POS = 154;
Running:
START SLAVE;
We look at the status:
SHOW SLAVE STATUS;
Similarly, you can configure the following slave servers, but you can dump it from the Slave server and stop it with the command:
STOP SLAVE;
Then we dump the database and look at the File and Position values to point them to the next Slave server:
SHOW SLAVE STATUS;
START SLAVE;
Replication is great for sending a part of SELECT requests instead of the main server to Slave, as well as for backing up a large database on the Slave server, so as not to stop the main database and not load it with it. Replication is not a backup.
The list of processes, connected clients and Slave can be viewed by the commands:
SHOW PROCESSLIST;
SHOW SLAVE HOSTS;
See also my article:
Solution of error “ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES”