Mysql master-slave configuration master configuration, mysql master-slave configuration master

Mysql master-slave configuration master configuration, mysql master-slave configuration master
I. Overview MySQL versions 3.23.15 and later provide the database replication function, which can be used to implement the synchronization, master-slave mode, and mutual backup mode of two databases. This document describes how to use mysql replication to configure hot standby in linux.II. EnvironmentOperating System: ubuntu 14.04 Mysql version: 5.6.14 device environment: PC (or virtual machine)Iii. ConfigurationThe settings of the Database Synchronization replication function are reflected in the MySQL configuration file. The MySQL configuration file (generally my. cnf) is/etc/my. cnf in the current environment. Set environment: IP settings: host a ip: 10.10.0.119 Mask: 255.255.255.0.0b Host IP: 10.10.8.112 Mask: 255.255.255.0.0 after the IP settings are complete, make sure that the firewalls of the two hosts are indeed disabled. You can run the service iptables status Command to view the Firewall status. If the firewall is still running. Use service iptables stop to stop the firewall. If you want to enable or disable the firewall, you can use the setup command to disable or customize the firewall. In the end, it is better for two hosts to ping each other. Configure master A (master B) from (slave) mode configure A to add A synchronization account to master 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, in my. add the following configuration to the cnf configuration item: server-id = 1 # Server id log-binbinlog-do-db = test # restart Database service mysqld restart to view serveridshow variable like 'server _ id'; instance: mysql> show variables like 'server _ id '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | server_id | 1 | + --------------- + ------- + 1 row in set (0.00 sec) check the status of the master database: 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) Configure B as slave modify configuration file: Modify B's/etc/my. cnf file, in my. add the following configuration to the cnf configuration item: server-id = 2replicate-do-db = test # Tell slave to only update the Mysql version of the backup database. The parameter similar to "master-host" is not supported since 5.1.7.
Execute change master to master_host = 'masterip', master_user = 'signal ', master_password = 'youslvaepass'; If the port is not 3306, add the port master_port = xxx to restart the 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 s View the logs by running the tatus/G command. Normal: mysql> show slave status/G **************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: masterIP MAID: 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 Condition: Yes condition: test, test condition: Last_Errno: 0 Last_Error: Skip_Counter: 0 condition: 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_SS Rochelle cipher: Master_SSL_Key: Seconds_Behind_Master: 01 row in set (0.01 sec) Verify that the configuration uses insert, delete, and update to add, delete, modify, and query databases on host; check whether the database of host B is consistent with that of host A. If yes, the configuration is successful. Dual-machine mutual backup (master-master synchronization) is added to slave settings on host A, and master Settings on host B can be added to synchronization between B->. FAQ and solutions 1. Slave machine permissions should be granted not only the File Permission but also the REPLICATION slave permission. 2. After modifying the Slave machine/etc/my. after cnf, remember to delete the master before the mysql service of the slave machine starts. info3. Check if the show master status or show slave status is abnormal. what is err. 4. Mysql Replication on Slave has two threads: I/O thread and SQL thread. I/O is used to retrieve its binlog from the master port 3306 (after the master has modified anything, write the modified information to your binlog for slave update) and write it to the local relay-log, while the SQL thread reads the local relay-log, the conversion cost is what Mysql can understand, So synchronization is completed step by step. decide I/O thread is/var/lib/mysql/master.info, and decide the SQL thread is/var/lib/mysql/relay-log.info. 5. start slave and run the start slave command. restart and use restart slave. If Slave_IO_Running: Yes (no) Slave_ SQL _Running: No (no) Method 1: Stop salvestop slave; # indicates that the skip step is incorrect. The following numbers can be set global SQL _slave_skip_counter = 1; start slave; then use show slave status \ G # Check the status Slave_IO_Running: YesSlave_ SQL _Running: Yes. Method 2: add slave-skip-errors = route 1146, skip error method 3: redo Master/slave synchronization reference http://blog.csdn.net/nuanchun666/article/details/2069301 In the mysql configuration file