Master-slave synchronization configuration of MySQL Databases in Linux

Master-slave synchronization configuration of MySQL Databases in Linux

Master-slave synchronization configuration of MySQL Databases in Linux

1. Configure the mysql master database #172.30.65.122

Mysql-u root-p # enter the Console

Create database cesudb # create a database speed database

Insert into mysql. user (Host, User, Password) values ('localhost', 'cesuuser', password ('20140901'); # create a user cesuuser with a Password of 123456

Grant all on cesudb. * to 'cesuuser' @ '172. 30.65.169 'identified by '000000' with grant option; # grant cesuuser full access to cesudb on the machine of nagios server 172.30.65.169.

Insert into mysql. user (Host, User, Password) values ('localhost', 'cesudbbak', password ('000000'); # create the mysql database master-slave synchronization user cesudbbak, and the User Password is 123456.

Grant replication slave on *. * to 'cesudbbak' @ '172. 30.65.124 'identified by '000000' with grant option; # authorize cesudbbak users to access the master database on machines 172.30.65.124 and only have backup permissions.

Flush privileges; # refresh the System Authorization Table

2. Import the database cesudb on Mysql master server 172.30.65.122 to Mysql slave server 172.30.65.124.

Note: You can run the following commands on the console

Flush tables with read lock; # Database read-only lock command to prevent data writing from everywhere.

Export the database cesudb

Mysqldump-u root-p -- default-character-set = utf8 -- opt-Q-R -- skip-lock-tables cesudb>/home/cesudbbak. SQL # Run the shell> command on the master database server to export the database cesudb to the/home directory and rename it cesudbbak. SQL.

Unlock tables; # unlock and run the command on the mysql Console

Scp cesudbbak. SQL root@172.30.65.124:/home # To/home directory to upload the file from the database 172.30.65.124/home directory.

2. Go to the slave database #172.30.65.124
Mysql-uroot-p

Create database cesudb; # create a database cesudb

Use cesudb; use the database cesudb

Source/home/cesudbbak. SQL # import the backup file to the database

Mysql-u cesudbbak-h 172.30.65.122-p # test logging on to the database master server 172.30.65.124 on the slave Database Server 172.30.65.122. The command must be executed under shell>.

3. Configure the my. cnf file of the master database server 172.30.65.122.

Vim/etc/my. cnf # Here, mysql on the local machine is installed in yum. If you compile and install it, you need to find the main configuration file and modify it.
Change the files in the directory to the following.
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0
Server-id = 1 # the row to be added. This row is set to 1, indicating the master server.
Log-bin = mysql-bin # the row to be added. Start the mysql binary log system.
Binlog-do-db = cesudb # Name of the database to be synchronized for the row to be added. You can add this row again if multiple databases exist.
Binlog-ignore-db = mysql # the row to be added. The mysql System database is not synchronized.
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

Service mysqld restart # restart the mysql service

Mysql-u root-p # enter the mysql Console

Show variables like 'server _ id'; # Check whether the server_id value is 1
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 1 |
+ --------------- + ------- +
1 row in set (0.00 sec)

Show master status # view master server information. The following information is displayed:

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000006 | 106 | cesudb | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Note: Remember the values of the above File and Position, which will be used later.

4. Configure the my. cnf file from the database #172.30.65.124

Vim/etc/my. cnf
Modify it to the following content. The slave database on this machine is also installed in yum. If the database on your machine is compiled and installed, you can set the configuration file location separately.
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0
Server-id = 2 # set the value of the row to be added to 2, indicating that the row is from the database
Log-bin = mysql-bin # Start the mysql binary log system
Replicate-do-db = cesudb # Name of the database to be synchronized. If there are multiple databases, you can repeat this parameter. Each database has one row.
Replicate-ignore-db = mysql # Do not synchronize the mysql System Database
Read_only # Set database read-only
# Bind-address = 172.30.65.124
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

Service mysqld restart # restart the database

Mysql-u root-p # enter the mysql Console

Show variables like 'server _ id'; # If the id is 2, it indicates normal.
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 2 |
+ --------------- + ------- +
1 row in set (0.00 sec)

Slave stop; # stop the slave synchronization process

Change master to master_host = '2017. 30.65.122 ', master_user = 'cesudbbak', master_password = '000000', master_log_file = 'mysql-bin.000006 ', master_log_pos = 123456; # Run the synchronization statement, master_log_file and master_log_pos are the connection values mentioned above.
Mysql> change master to master_host = '192. 30.65.122 ', master_user = 'cesudbbak', master_password = '000000', master_log_file = 'mysql-bin.000006', master_log_pos = 172;
Query OK, 0 rows affected (0.02 sec)

Slave start; # enable the slave synchronization process

Show slave status \ G # view the synchronization information. The following synchronization information appears:
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.65.122
Master_User: cesudbbak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: cesudb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)
Note that the red part appears. If yes is displayed for the above parameters, the configuration is successful.

V. Test Master/Slave Configuration

1. Create a table student #172.30.65.122 on the primary database.
Mysql-u root-p

Use cesudb;

Create table... # create a test table
Mysql> create table student (
-> Id int,
-> Name char (10 ),
-> Sex char (2 ));
Query OK, 0 rows affected (0.02 sec)
Mysql> show tables;
+ ------------------ +
| Tables_in_cesudb |
+ ------------------ +
| Student |
+ ------------------ +
1 row in set (0.00 sec)

2. log on to the database and view the table #172.30.65.124

Mysql> use cesudb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-
Database changed
Mysql> show tables;
+ ------------------ +
| Tables_in_cesudb |
+ ------------------ +
| Student |
+ ------------------ +
1 row in set (0.00 sec)

The synchronization is successful.

Implement master-slave synchronization between two MySQL Databases

MySQL master-slave synchronization in Linux -- Add a new slave Database

Use XtraBackup to build MySQL master-slave synchronization without stopping the table and locking it

MySQL master-slave synchronization configuration records

This article permanently updates the link address: