Mysql Master/Slave

Create a mysql master
Server‑mysql: 192.168.1.108
Server2_mysql: 192.168.1.110
Topology:
Serverslave MySQL ------- server2_mysql
1. Create and authorize a user
Server1:
Mysql> grant replication slave on *. * TO 'server2' @ '192. 168.1.110'
Identified by 'server2 ';
Mysql> flush privileges;
Server2:
Mysql> grant replication slave on *. * TO 'server1' @ '192. 168.1.108'
Identified by 'server1 ';
2. Modify the Mysql master configuration file/etc/my. cnf
 
Server1:
Log-bin = mysql-bin # binary logs must be enabled
Server-id = 1 # change the other id to 2
Binlog-do-db = test # enter the database to be synchronized
Binlog-ignore-db = mysql # enter the database that does not need to be synchronized
Replicate-do-db = test # enter the database to be synchronized
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 1 # increase by 2 each time
Auto_increment_offset = 1 # Set the offset of the automatically increasing field, that is, the initial value is 2
 
Start the Mysql service:
# Service mysqld restart
Server2:
Log-bin = mysql-bin # binary logs must be enabled
Server-id = 2
Binlog-do-db = test # enter the database to be synchronized
Binlog-ignore-db = mysql # enter the database that does not need to be synchronized
Replicate-do-db = test # enter the database to be synchronized
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2 # increase by 2 each time
Auto_increment_offset = 1 # Set the offset of the automatically increasing field, that is, the initial value is 2
Start the Mysql service:
# Service mysqld restart
Note: Only server-id and auto-increment-offset are different.
The value of auto-increment should be set to the total number of servers in the entire structure. In this case, two
Server, so the value is set to 2.
3. Restart two servers
# Service mysqld restart
4. To make the two databases the same, we back up one of the databases and restore them on the other database.
In this way, both databases are the same at the beginning.
Operate on Server 1:
# Mysqldump -- databases luowei>/tmp/luowei. SQL
Operate on Server 2:
Create an empty database with the same name as mydb
# Mysql
> Create database mydb;
> \ Q
# Scp 192.168.1.108:/tmp/mydb. SQL ?. /
# Mysql-uroot-p mydb </tmp/luowei. SQL
5. Then, the two servers advertise the location of the binary log and start the replication function:
On server1:
# Mysql
> CHANGE MASTER
> MASTER_HOST = '1970. 168.1.110 ',
> MASTER_USER = 'server2 ',
> MASTER_PASSWORD = 'server2 ';
Mysql> start slave;
On server2:
# Mysql
> CHANGE MASTER
> MASTER_HOST = '1970. 168.1.108 ',
> MASTER_USER = 'server1 ',
> MASTER_PASSWORD = 'server1 ';
Mysql> start slave;
6. View and verify:
View on two database servers respectively
Mysql> start slave;
View the database and table, and you will find that the content is the same. This is the architecture of the entire primary Mysql.
Configuration process.
 
 
Check master-slave Synchronization
 
[Root @ node1 ~] # Mysql # enter the mysql Command Line
 
Mysql> show master status; # display (different host results)
 
+ ------------------ + ---------- + ------------------- + ------------------ +
 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+ ------------------ + ---------- + ------------------- + ------------------ +
 
| Updatelog.000028 | 313361 | db1 | mysql |
 
+ ------------------ + ---------- + ------------------- + ------------------ +
 
(If you suspect that the Master/Slave Data is not synchronized before synchronization, you can use the cold Backup Remote COPY method or the synchronization method on the slave server)
 
Run the MySQL command on the slave server:
 
Mysql> slave stop; # stop the slave service first
 
Mysql> change master to MASTER_LOG_FILE = 'updatelog. 100', MASTER_LOG_POS = 000028;
 
# Perform a binary database record regression from the server based on the show master status result of the master server above to achieve synchronization.
 
Mysql> slave start; # start the slave server Synchronization Service
 
Mysql> show slave status \ G;
 
Use show slave status \ G; to check synchronization from the server
 
Slave_IO_Running: Yes
 
Slave_ SQL _Running: Yes
 
If all are yes, it indicates that the synchronization is in progress.
 
Using the mysql client for testing is much more convenient than using the command line.
Author: "guo Weijun's work and study records"