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