Mysql database AB replication Configuration

Use two mysql servers to implement AB and master-slave replication.
 
1. Configure the MASTER server
 
MASTER 172.16.1.3
BACKUP 172.16.1.2
 
1. Edit the my. cnf File
# Add the two rows on the original basis
 
[Root @ zhaoyun ~] # Cat/etc/my. cnf
[Mysqld]
Log-bin =/mysql/bin # enable binlog
Server-id = 1 # The configuration does not repeat with another one.
2. Restart the service
[Root @ zhaoyun ~] # Service mysqld restart
Stop MySQL: [OK]
Start MySQL: [OK]
3. Authorized Users
Mysql> grant replication slave on *. * to zhaoyun@172.16.1.2 identified by '20140901'
[Root @ zhaoyun ~] # Mysql-uroot-p123456
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 2
Server version: 5.0.77-log Source distribution
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>
Mysql> grant replication slave on *. * to zhaoyun@172.16.1.2 identified by '2013 ';
Query OK, 0 rows affected (0.15 sec)
Mysql>
4. Test whether you can log on to server B.
[Root @ BACKUP ~] # Mysql-uzhaoyun-p123456-h172.16.1.3
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 4
Server version: 5.0.77-log Source distribution
Copyright (c) 2000,201 0, Oracle and/or its affiliates. All rights reserved.
This software comes with absolutely no warranty. This is free software,
And you are welcome to modify and redistribute it under the GPL v2 license
Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.
Mysql> show grants;
+ Shards +
| Grants for zhaoyun@172.16.1.2 |
+ Shards +
| Grant replication slave on *. * TO 'zhaoyun '@ '2017. 16.1.2 'identified BY password'565491d704013245' |
+ Shards +
1 row in set (0.00 sec)
Mysql>
5. view the master Status
Mysql> show master status;
+ ------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------ + ---------- + -------------- + ------------------ +
| Bin.000001 | 315 |
+ ------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
The file field is the binlog file Name of the master, and the position is the node of the binlog.
Ii. Configure BACKUP
1. Edit the configuration file my. cnf and add four lines.
[Mysqld]
Server-id = 2
Master-host = 172.16.1.3 # IP address of the MASTER server
Master-user = zhaoyun # username used to connect to the MASTER server
Master-password = 123456 # password
2. Restart the service
[Root @ BACKUP ~] # Service mysqld restart
Stopping mysqld: [OK]
Starting mysqld: [OK]
3. After the service is restarted, several files will be generated in the database directory.
[Root @ BACKUP mysql] # ls
Ib_logfile1 mysqld-relay-bin.000001 mysqld-relay-bin.index test
Ibdata1 master.info mysql. sock
Ib_logfile0 mysql relay-log.info.
[Root @ BACKUP mysql] # pwd
/Var/lib/mysql
Mysqld-relay-bin.000001 # binload file, copied from master
Mysqld-relay-bin.index # binload Information
Master.info # master Information
Relay-log.info # relay log information
4. view the slave status
[Root @ BACKUP ~] # Mysql-uroot-p123456
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.3
Master_User: zhaoyun
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 315
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 453
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 315
Relay_Log_Space: 453
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
1 row in set (0.00 sec)
ERROR:
No query specified
Mysql>
# This is the status of the binlog file of the main service. If I/O is NO, check the status of the two files.
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 315
 
# The two are slave IO processes and SQL process statuses. The AB replication service is only available when yes is used.
 
Slave_IO_Running: YES
Slave_ SQL _Running: YES
# If the IO process is NO, you can delete the BACKUP data file and restart the service to re-synchronize it.
 
5. Now the configuration is complete.
 
3. Create a table and test whether the synchronization is successful.
1. Create on the master.
Mysql> create database master;
Query OK, 1 row affected (0.00 sec)
Mysql> use master
Database changed
Mysql> create table master (id int, name char (5 ));
Query OK, 0 rows affected (0.04 se
2. View in backup
[Root @ BACKUP ~] # Mysql-uroot-p123456
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Master |
| Mysql |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)
Mysql> use master
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_master |
+ ------------------ +
| Master |
+ ------------------ +
1 row in set (0.00 sec)
Mysql>
We can see that the data has been synchronized. This experiment is successful.
 
Troubleshooting:
 
IO is equal to NO: Check whether the node and binlog file names are consistent with those seen on the master. If they are inconsistent, manually rewrite them.
Command
Stop the slave service first
Mysql> slave stop;
Mysql> change master to master_log_file = "binlog file name displayed on master ";
Mysql> change master to master_log_pos = 100; this number is displayed on the master.
Mysql> slave start;
Mysql> show master status;
+ ------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------- + ---------- + -------------- + ------------------ +
| Mysqld-bin.000001 | 507 |
+ ------------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
SQL is equal to NO. You can try to delete several files and restart the service to re-Synchronize.
Mysqld-relay-bin.000001 # binload file, copied from master
Mysqld-relay-bin.index # binload Information
Master.info # master Information
Relay-log.info # relay log information
This article is from the "Technical Exchange" blog