MySQL data synchronization, replication, distribution

MySQL master-slave replication:
1, the master and slave servers are enabled binary log, set a unique ID, and establish a connection with each other account and authorization;
2, from the server actively to the primary server request query the current synchronization state, and pull back data;

Main related commands:

查看主服务器状态：show master status\G;查看从服务器状态：show slave status\G;查看mysql I/O线程：show processlist\G;配置从服务器复制：change master to {***}详见后面说明启动从服务器复制：start slave;授权用户：grant 权限 on 数据库.* to 用户名@‘登录主机‘ identified by "密码";lock;解除锁定库：unlock tables;

Related Settings Primary server:

#vi /etc/my.cnf[mysqld]log-bin=mysql-bin                     //[必须]启用二进制日志server-id=222                             //[必须]服务器唯一ID，默认是1，一般取IP最后一段binlog-do-db=wordpress         //表示只备份wordpressbinlog_ignore_db=mysql         //表示忽略备份mysql不加binlog-doONto‘copy_user‘@‘%‘by‘copy_password‘;    --这里要注意：REPLICATION SLAVE，指主服务器复制权限mysql>show processlist;         --查看已连接的从服务器

From the server:

#vi/etc/my.cnf[mysqld]log-bin=mysql-bin//[is not required] to enable binary logging, when the slave server is the primary server of another server, you must set theServer-id=111             //[must] server unique ID, default is 1, usually take IP last paragraphslave-Skip-errors =1062    //[not required] Ignore duplicate key value errorreplicate- Do-db=testreplicate-ignore-db=mysqlmysql>grant REPLICATION CLIENT on*.* to ' Copy_user '@'% 'Identified by ' Copy_password '; --Note here: REPLICATION client refers to copy permissions from server Mysql>change Master to--Configure which primary server to connect to->master_host=' 192.168.2.12 ',--Home server IP address->master_user=' Copy_user ',--The user name configured in the master server->master_password=' Copy_password ',--Password->master_log_file= in the master server' mysql-bin.000039 ',--that is, the current primary server log file name, which is copied from this file, not greater than the front file value. ->master_log_pos=32176;                                                        --Starting from the corresponding position in the log file above, the front position value, Mysql>start slave; --Start copy function from server]mysql>stop slave;mysql>SET GLOBALSql_slave_skip_counter=1; --Ignore1Step Error

to master_host=‘192.168.1.21‘,master_user=‘copy_user‘,  master_password=‘copy_password‘,master_log_file=‘mysql-bin.000031‘, master_log_pos=108506;mysql>start slave;

To view the primary server status, run on the primary server:

mysql> show master status\G;*************************** 1. row ***************************             File: mysql-bin.000008         Position: 154     Binlog_Do_DB:  Binlog_Ignore_DB: mysql,sys,information_schema,performance_schemaExecuted_Gtid_

To view the current replication status from the server, run from the server:

Mysql> Show Slave Status\g;***************************1.Row ***************************slave_io_state:Waiting for master to send eventMaster_host:                     192.168. 2.Primary server addressMaster_user:Copy_king//Authorized account nameMaster_port:                      3306Database portConnect_retry:                   -How long does the interval retry when the connection is incorrectMaster_log_file:Mysql-bin. 000038Maximum log ID of the current primary serverRead_master_log_pos:     95990The log location to which the current master server is located, which is the same as the value in the primary serverRelay_log_file:Kq126-relay-bin. 000053Your own log locationRelay_log_pos:                 96153Synchronously reads the location of the binary log, greater than or equal to Exec_master_log_posRelay_master_log_file:Mysql-bin. 000038Synced to the home server locationslave_io_running:Yes//This state must be yes to connect to the main library if it is runningslave_sql_running:Yes//This state must be yes, the write state in this library is running, both must be yes, as long as there is no, there is a problemreplicate_do_db: replicate_ignore_db: replicate_do_table: replicate_ignore_table: replicate_wild_do_table: replicate_wild_ignore_table: Last_errno: 0Last_error: Skip_counter: 0Exec_master_log_pos:                  95990//Relay_log_space:                         96489until_condition:NoneUntil_log_file: Until_log_pos: 0master_ssl_allowed:NoMaster_ssl_ca_file: Master_ssl_ca_path: Master_ssl_cert: Master_ssl_cipher: Master_ssl_key: Seconds_behind_master: 0How many seconds are delayed from server data than the primary server, typically0Master_ssl_verify_server_cert:NoLast_io_errno: 0Last_io_error: Last_sql_errno: 0Last_sql_error: Replicate_ignore_server_ids: master_server_id: 9Master_uuid: 7d911e39-3a98-11e5-8b9e- theC29168ea6Master_info_file:/usr/local/mysql/var/master. Info--Files currently configured for the main librarySql_delay: 0Sql_remaining_delay:Nullslave_sql_running_state:Slave have read all relay log; Waiting for the slave I/O thread to update itMaster_retry_count: 86400Master_bind: Last_io_error_timestamp: Last_sql_error_timestamp: MASTER_SSL_CRL: Master_ssl_crlpath: Retrieved_gtid_set: Executed_gtid_set: auto_position: 01Rowinch Set(0.00 sec)ERROR:No query specified

To view the logs in Linux:

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000050

Restore via log:

Restore by Time:
–start-datetime= "Starting Time to restore data"
–stop-datetime= "End time to restore data"
Mysqlbinlog–start-datetime= "Time" Log file path | Mysql-u User-p password – Restore from the specified start time to the present
Mysqlbinlog–stop-datetime= "Time" Log file path | Mysql-u User-p password – Restore from the beginning to the specified end time
Mysqlbinlog–start-datetime= "Time" –stop-datetime= "time" Log file path | Mysql-u User-p password – Specify the time period

[root@localhost bin]# /usr/local/mysql/bin/mysqlbinlog --stop-datetime=‘2015-10-19 17:30:0‘ /usr/local/mysql/var/mysql-bin.000001 | mysql -uroot -proot

Restore by Location:
–start-position= "Starting location for restoring data"
–stop-position= "Ending location of restore data"
mysqlbinlog–start-position= "Location" Log file path | Mysql-u User-p password – Restore from the specified starting position to the present
mysqlbinlog–stop-position= "Location" Log file path | Mysql-u User-p password – Restore from the beginning to the specified end location
mysqlbinlog–start-position= "Location" –stop-position= "Location" Log file path | Mysql-u User-p password – Specify the location segment

MySQL data synchronization, replication, distribution