MySQL Master-Slave synchronization

I. MySQL master-slave synchronization
Scenario: one Master one from
A master more from
Slave (master) from


Master slave
172.30.13.8 Database service 172.30.13.191 database service
The database server on the slave role must have a library on the primary database server, a table-table structure consistent
(You must have libraries and tables for the primary database server from the database server)



1. Configure the master database server
1.1 Turn on Binlog log (must be enabled)
1.2 Authorized users can connect their own copy data from the slave database server
mysql-uroot-p999
Grant Replication Slave on * * to [e-mail protected] "172.30.13.191" identified by "321";
Show master status; Log related information required to query configuration slave

1.3 Specify your own server_id value (range: 0-255)
[Email protected] ~]# CAT/ETC/MY.CNF
[Mysqld]
Log-bin=plj
Server_id=8
[[Email protected] ~]# service MySQL restart



Configuring the Slave database server
1 Specifying your own server_id value
2 Turn on Binlog log (optional)
[Email protected] desktop]# CAT/ETC/MY.CNF
[Mysqld]
Log-bin
server_id=191
[[Email protected] desktop]# service MySQL restart
Show master status; View Binlog log information currently in use

3 Specify who you do from the database server
Mysql-uroot-p123
Mysql> Change Master to
-master_host= "172.30.13.8",
-Master_user= "PLJ",
-master_password= "321",
-master_log_file= "plj.000001",
master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

Mysql> mysql> show Slave status\g;
Slave_io_running:no
Slave_sql_running:no
mysql> start slave;
Mysql> mysql> show Slave status\g;
Slave_io_running:yes
Slave_sql_running:yes



Second, master-slave synchronization of the working principle
Master.info record information to connect to the primary database server
Host name-relay-bin.000001 trunk log SQL statement
Hostname-relay-bin.index Record the existing trunk log file name
Log file information used by the Relay-log.info record
Slave_io_running:yes is responsible for the main database server binlog log SQL statements, copied to their native relay-bin log file
Slave_sql_running:yes is responsible for executing this machine's relay-bin log, the SQL statement in the file


Test: Whether the data on the primary database server can be automatically synchronized from the database server.
Mysql-h172.30.13.8-u User name-p password
Note: Change Master to ... That is, you can specify the primary server, or you can modify the server information that you have specified

Third, trouble-shooting
1.slave_io_running:no
The physical device is not connected
Firewall
SELinux
Incorrect authorization information

2.slave_sql_running:no
Inconsistent master-slave library and table structure
Master must enable Bin-log log


Four: Specify synchronization libraries and tables
1. The license synchronization user must be *. *
2. On the primary database side setting allows you to back up your own library data from the database server;

Vim/etc/my.cnf
Binlog-do-db=name//To make a backup of your own library from the data device
Binlog-ignore-db=name//Do not allow backup of your own library from the data device
Note: The above two options cannot be used simultaneously


Or, on the database server side set up their own backup of the primary database which data;
Vim/etc/my.cnf
Replicate-do-db=mysql//Specifies which library on the primary database server to back up only
Replicate-ignore-db=mysql//Do not back up which library on the primary database server






Example 1: Master > Slave (Master) > from
1. Two primary server set authorization (otherwise, after the logging function, the content will be synchronized to the slave server, causing security risks)
Grant Replication Slave on * * to [e-mail protected] "172.30.13.191" identified by "321";
Grant Replication Slave on * * to [e-mail protected] "172.30.13.192" identified by "321";

Show master status; (Querying log files and I points in use)

2. Two primary server open log (Bin-log) and set ID (server_id=)
3. Add the Log-slave-updates statement from the (primary) server in MY.CNF, and when added, the synchronized slave log content will be synchronized to the local log, or the master-slave from the experiment
4. From the server configuration see above master-slave synchronization;

This article is from the "Zhengerniu" blog, make sure to keep this source http://liufu1103.blog.51cto.com/9120722/1656835

MySQL Master-Slave synchronization