I. Overview MySQL provides database replication (replication) functionality from the 3.23.15 release, which enables two database synchronization, master-slave mode, and backup mode. This document mainly describes how to use MySQL's replication in Linux system for the configuration of dual-machine hot standby.
Second, the environmentoperating system: Ubuntu 14.04MySQL version: 5.6.14Device Environment: PC (or virtual machine) two
Third, the configurationThe settings for the database replication feature are reflected in the MySQL configuration file, MySQL configuration file (typically MY.CNF): In this environment for/etc/my.cnf . Set settings for Environment: ip : A host IP:10.10.0.119 mask:255.255.0.0b host ip:10.10.8.112 mask:255.255.0.0 after the IP setting is complete, you need to determine that the two host's firewall is indeed turned off. You can use the command service iptables status to view the firewall status. If the firewall status is still running. Use the service iptables stop to deactivate the firewall. If you want to start the firewall off, you can use the Setup command to disable or customize it. Finally, two hosts can ping each other to be better. Configure a Master (master) b from (slave) Mode config A for master add a sync account using Grant FILE on *. * to ' signal ' @ ' SLAVEIP ' Identified by ' Youpassword '; GRANT REPLICATION SLAVE on * * to ' signal ' @ ' SLAVEIP ' identified by ' Youpassword '; FLUSH privileges; Modify the/etc/my.cnf file of a, add the following configuration in the my.cnf configuration: Server-id = 1 #Server Identification log-binbinlog-do-db=test #指定需要日志的数据库 Restart database service mysqld Restart view Serveridshow variable like ' server_id '; example:mysql> show variables like ' server_id '; +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 1 |+---------------+-------+1 row in Set (0.00 sec) View Primary database status Show Master Status\g normal: MySQL > Show Master status/g*************************** 1. Row ***************************file:mysqld-bin.000002position:198binlog_do_db:test,testbinlog_ignore_db:1 Row in Set (0.08 sec) Config B modify the configuration file for slave: Modify the/etc/my.cnf file of B, in my.cnf Configuration items are included in the following configuration: Server-id=2replicate-do-db=test #告诉slave只做backup数据库的更新 mysql version from 5.1.7 does not support "Master-host" similar parameters
Perform change master to master_host= ' Masterip ', master_user= ' signal ', master_password= ' youslvaepass '; If the port is not 3306 Need to add port master_port=xxx Restart database service: service mysqld restart View server-id: show variables like ' Server_ ID '; instance:mysql> show variables like ' server_id '; +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+1 row in Set (0.00 sec) Use show slave status/g command to see the log situation. normal for:mysql> show slave status/g*************************** 1. Row *************************** Slave_IO _state:waiting for Master to send event master_host:masterip Master_user:signal master_port:3306 connect_retry:60 master_log_file:mysqld-bin.000001 read_master_log_pos:98 Relay_Log_ file:mysqld-relay-bin.000003 relay_log_pos:236 relay_master_log_file:mysqld-bin.000001 slave_io_running:yes slave_sql_running:yes Replicate_do_db:test, test 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:98 relay_log_space:236 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:01 Row in Set (0.01 sec) authentication configuration using INSERT, delete, update in a& nbsp; The host makes the database to be increased and censored; check whether the database of Host B is consistent with host a, and if so, the configuration is successful. Two-Machine Interoperability (master synchronization) in a host to join the slave settings, the host is added to the master setting, you can do b->a synchronization. Common problems and solve 1, slave machine permissions problem, not only to slave machine file permissions, but also to give it replication slave permissions. 2, after modifying the slave machine/etc/my.cnf, slave machine after the start of the MySQL service, remember to delete Master.info3, in Show Master status or show slave status When it's not normal, look at how err is said. 4, slave on MySQL replication work has two threads, I/O thread and SQL thread . i/o 's role is to take its binlog from the Master 3306 port (Master has modified anything to write to its own binlog wait for slave update), and then write to the local relay-log, and SQL Thread is to read the local relay-log, and then convert it to the cost of MySQL can understand the statement, so the synchronization of such a step-by-step completion. Determine I/O thread is/var/lib/mysql/master.info, while the decision SQL thread is/var/lib/mysql/relay-log.info. 5, start slave, command with start slave Restart with restart Slave if you encounter Slave_io_running:yes (NO) slave_sql_running:no (No) Method 1: Stop Salvestop Slave; #表示跳过一步错误, the subsequent number variable set Global Sql_slave_skip_counter =1;start slave, then show slave status\g #查看状态Slave_IO_Running: yesslave_sql_running: Normal Method 2: Add slave-skip-errors=1062,1053,1146 Skip Error Method 3: Re-master from Sync reference http://in MySQL config file blog.csdn.net/nuanchun666/article/details/2069301
MySQL master-slave configuration primary master configuration
