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&NBSP;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