MySQL master-slave replication principle and practice

MySQL Master-slave replication

MySQL supports unidirectional two-way chaining cascading real-time asynchronous replication, where one server acts as the primary server (master) during replication, while one or more other servers act as slave servers (Slave)

MySQL master-slave replication application Scenario

1, the master-slave server for each other backup

2, master-slave server read-write separation share site pressure

Read/write separation

Medium and large companies: through the program (Php,java)

Test environment: Agent Software (Mysql-proxy,amoeba)

Portal: Distributed Dbproxy (read/write separation, hash load balancing, health check)

Master-Slave Synchronous Practice operation (Multi-instance environment) 1, set the Server-id value above the main library and turn on the Binlog parameter [[email protected] ~]# egrep  "log-bin| Server-id " /data/3306/my.cnf log-bin = /data/3306/mysql-binserver-id = 1 Check the actual configuration effect [[email protected] ~]# mysql -uroot -p123456 -s /data/3306/mysql.sock  -e  "show variables like  ' Log_bin ';"  +---------------+-------+| variable_name | value |+---------------+-------+|  log_bin       | on    |+---------------+-------+ 2, Create an account for synchronization Mysql>  grant replication slave  on *.* to [email  protected] ' 172.16.80.% '  identified by  ' 123456 '; description:replication slave  is a required permission for MySQL synchronization, do not authorize allmysql> flush privileges; to view the results after authorization mysql> show grants for  [email protected] ' 172.16.80.% '; +--------------------------------------------------------------------------------------------------------------------------+| grants for [email  protected]%                                                                                                    |+------------------------------------------------------------ --------------------------------------------------------------+| grant replication slave  on *.* to  ' rep ' @ ' 172.16.80.% '  IDENTIFIED BY PASSWORD  ' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' &NBsp;|+---------------------------------------------------------------------------------------------------------- ----------------+1 row in set  (0.00&NBSP;SEC) 3, lock table, export database mysql> flush table  with read lock;     #该窗口不能断, open a new window to do the database export operation Query ok, 0 rows  affected  (0.00 sec) mysql> show master status;+------------------+----------+ --------------+------------------+| file              | position | binlog_do_db | binlog_ignore_db |+--------------- ---+----------+--------------+------------------+| mysql-bin.000002 |       332 |              |                   |+------ ------------+----------+--------------+------------------+1 row in set  (0.00 sec) [[email protected]  ~]# mysqldump -uroot -p123456 -s /data/3306/mysql.sock -a -b -- events --master-data=2 > /opt/rep.sql  #导出所有数据库 [[Email protected] ~]# vim  /opt/rep.sql-- change master to master_log_file= ' mysql-bin.000002 ',  MASTER_LOG_ pos=332;   #可以看到该语句的记录位置和上面show  master status is the same, note State 4, after the database is exported, unlock mysql> show  master status;    #再次查看位置点 to verify that the above lock table operation is valid +------------------+----------+--------------+---- --------------+| file             |  position | binlog_do_db | binlog_ignore_db |+------------------+----------+---- ----------+------------------+| mysql-bin.000002 |      332 |               |                   |+------------------+----------+--------------+--- ---------------+1 row in set  (0.00 sec) mysql> unlock tables; query ok, 0 rows affected  (0.00&NBSP;SEC) 5, from the top of the library [[email protected] ~]#  egrep  "Log-bin|server-id"  /data/3307/my.cnf      #log-bin = /data/ 3307/mysql-bin     #log-bin without opening server-id = 3   # The value of Server-id cannot be the same as the value above the main library [[Email protected] ~]# mysql -uroot -p123456 -s /data /3307/mysql.sock < /opt/rep.sql    #导入从主库备份的数据库 [[email protected] ~]#  mysql -uroot -phello123 -S /data/3307/mysql.sock              mysql> change master to     -> master_host= ' 172.16.80.118 ',     -> master_user= ' rep ',     -> master_password= ' 123456 ',     -> master_log_file= ' mysql-bin.000002 ',    ->  master_log_pos=332; query ok, 0 rows affected  (0.03 sec) Verify [[email protected] ~]#  cat /data/3307/data/master.info 18mysql-bin.000002332172.16.80.118rep1234563306600mysql>  start slave;      query ok, 0 rows affected  (0.01 &NBSP;SEC) mysql> show slave status\g;     #观察Slave_IO和Slave_SQL   Whether the state of these two threads is yes*************************** 1. row ***************************                slave_io_state: waiting for  master to send event                   Master_Host: 172.16.80.118                   Master_User: rep                   master_port: 3306                 connect_retry:  60              master_log_file:  mysql-bin.000002          Read_Master_Log_Pos:  332               relay_log_file:  relay-bin.000002                 relay_log_pos: 253        relay_master_log_file: mysql-bin.000002              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB: mysql            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: 332               Relay_Log_Space: 403               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: 0Master_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: 11 row in set  (0.00&NBSP;SEC) 

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/86/4C/wKiom1e7gazQ_uksAAIQOCL_AuU791.png "title=" 1. PNG "alt=" Wkiom1e7gazq_uksaaiqocl_auu791.png "/>

6, log in to the main library to create a database to see if it will be synced to the top of the library [[email protected] ~]# mysql -uroot -p123456 -s / Data/3306/mysql.sock mysql> create database martin; query ok, 1 row  affected  (0.01 SEC) observation from the library, you can see that it has been synchronized mysql> show databases;+--------------------+|  database           |+--------------------+|  information_schema | |  martin             | |  mysql              | |  performance_schema | |  test               |+-------- ------------+5 rows in set  (0.00 sec) [[email protected] ~]# cd / Data/3307[[email protected] 3307]# lsdata  my.cnf  mysql  mysqld.pid  mysql_martin3307.err  mysql.sock  relay-bin.000001   relay-bin.000002  relay-bin.index  relay-log.info[[email protected] 3307]#  cat data/master.info 18mysql-bin.000002419172.16.80.118rep1234563306600[[email protected]  3307]# mysqlbinlog  relay-bin.000002/*!\c utf8 *//*!*/; set @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!* /; set @ @session. lc_time_names=0/*!*/; set @ @session. Collation_database=default/*!*/;create database martin

MySQL master-slave replication Principle Summary:

1. Asynchronous Synchronous mode

2, the logical synchronization mode, a variety of modes, by default is executed by the SQL statement

3, the main library through the record Bin-log implementation of the synchronization from the library, Bin-log record database update statements

4. One IO thread from the main library, one IO thread from the library and one SQL thread

5, from the library key files Master.info relay-log Relay-info

6, if from the library also need to do cascade from the library, from the library need to open the Log-bin and log-slave-updates parameters

This article from "Thick tak" blog, declined reprint!

MySQL master-slave replication principle and practice