MySQL Master-Slave synchronization

What is MySQL master-slave synchronization?

Synchronize data from the library on the main library (to have other database servers automatically synchronize data on the data server that is currently being accessed)

Structure type of master-slave synchronization

1 primary------>1 from (common) one primary server (responsible for business updates) one from the server

1 master-------> multiple Slave (Common) one primary server (responsible for business updates) multiple slave servers

Master-------> from--------> from one Primary server (responsible for business updates) one from the server (also as the other primary server) from the server

Master <==> Master (not used alone, generally with third-party software to do high-availability clustering or load balancing) server to each other from the server

Environmental requirements

1.mysql Server version needs to be the same

2. Network can interconnect

3. Firewalls and SELinux need to be released MySQL lab environment can shut down selinux and firewalls all

4. Before configuring the master-Slave synchronization structure, be sure to let the same data from the inject and the main library (if two server data structures are different on the primary server can execute the SQL on the slave server, the SQL thread will no, no longer synchronize)

Primary database server

1. User authorization

Grant replication Slave on . to authorized user @ "from server IP address" identified by "password"

2. Enable Binlog logging

Vim/etc/my.cnf

[Mysqld]

server_id=0-255 the value of the master never repeats

log-bin=/Specify the folder/log file name of the Binlog log the MySQL service user needs to have the read and write execution permissions for the specified folder to be specified by default in the database directory/var/lib/mysql

binlog-format= "mixed" Logging using mixed Mode (mixed-mode recording of change results and SQL commands)

3. View the Binlog log in use

Show master status;

From the database server

1. Verifying the Master Library authorization

#mysql-H Main Library address-u authorized user-P authorization password;

On the command line from the server can login instructions for authorization success

2. Specify server_id

Vim/etc/my.cnf

[Mysqld]

server_id=52

: Wq

show slave status; View yourself is not from the library information

Because we haven't specified any information yet.

3. Specify the main library information

Mysql> Change Master to
-Master_user= "Repluser",//master server authorized user
-master_host= "192.168.4.51",//home Server IP Address
-master_password= "123456",//Authorization password
-master_log_file= "a1-bin.000001",//the Binlog log that the primary server is using can show master status on the primary server to see the log being used and the offset that has been recorded
-Master_log_pos=452;//binlog log already recorded offset
Query OK, 0 rows affected, 2 warnings (0.23 sec)

4. View the configuration

show slave status\g;
1. Row
Slave_io_state:
master_host:192.168.4.51
Master_user:repluser
master_port:3306
Connect_retry:60
master_log_file:a1-bin.000001
read_master_log_pos:452
relay_log_file:a2-relay-bin.000001
Relay_log_pos:4
relay_master_log_file:a1-bin.000001
Slave_io_running:no
Slave_sql_running:no
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:452
relay_log_space:154
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:null
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:0
Master_uuid:
Master_info_file:/var/lib/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
replicate_rewrite_db:
Channel_name:
Master_tls_version:
1 row in Set (0.00 sec)
5. With Salve process

mysql> start slave;
Query OK, 0 rows affected (0.08 sec)

Check the configuration again when the values of the following two parameters are Yes

         Slave_IO_Running: YES   //IO线程            将主服务器上的binlog日志（由主服务器上的  Binlog Dump 推送新的日志记录）的sql命令记录到本机的中继日志文件里 /var/lib/mysql/主机名-relay-bin.编号        Slave_SQL_Running: YES //SQL线程

Verifying the configuration

Create a database on the primary server and a database created on the primary server from the server

Common causes of Errors

I/O threads do not come up

Error configuring parameters will

Firewall

SELinux

If there are any errors, the following parameters may cause a log error

show slave status\g;

           Last_IO_Errno: 0           Last_IO_Error:           Last_SQL_Errno: 0           Last_SQL_Error:

SQL thread does not come up

The database is not unified before the master-slave configuration, resulting in conflicts

Workaround stop Slave;

Restore the database from the server to the consolidated database on the master server first

Restart start slave;

One more master from the other is to add a slave server

Create an authorized user from the server on the primary server

Configure a second slave database server

Primary database server Partial configuration file parameters

[Mysqld]

binlog_do_db= Library Name list//allow libraries to be synchronized from

binlog_ignore_db= Library Name list//not allowed from a synchronized library

Two parameters are not written to synchronize all libraries

Configuration file parameters from the database server section

[Mysqld]

binlog_do_db= Library Name list//library only from Master Sync

binlog_ignore_db= Library Name list//library not synchronized from master

Two parameters are not written to synchronize all libraries

Log_slave_updates//Cascade replication executes the SQL command in the trunk log when logged to the Binlog log by default Binlog does not log the SQL that performs the relay log (this parameter needs to be configured from the server from the middle of the structure)

MySQL Master-Slave synchronization