Steps for configuring mysql master-slave synchronization in linux

Step 1 of configuring mysql master-slave synchronization in linux: host environment HOST: master Operating System: rhel6.0IP: 172.16.0.100 MySQL version: 5.1.47 slave: www.2cto.com slave Operating System: rhel6.0IP: 172.16.0.200 MySQL version: 5.1.47 2. create a database and log on to the mysql: mysql-u root-p of the master and slave respectively to create a database: create database repl;
3. Configure the master and slave machines. 1. Modify the mysql configuration file my. cnf, in the/etc directory, add the following field in the [mysqld] configuration section: server-id = 1 log-bin = mysql-bin binlog-do-db = repl // the file to be synchronized database, if no row exists, it means to synchronize all database binlog-ignore-db = mysql // The ignored database and add a synchronization Account grant replication slave on * to the slave machine on the master machine *. * to 'replicase' @ '172. 16.0.200 'identified by '200'; restart the mysql service of the master machine: service mysqld restart use the show master status Command to view the log. mysql> show master status;
+ Response + ------------ + response + | File | Position | Binlog_Do_DB | response | + ----------------- + ---------- + response + | log.20.03 | 98 | repl | mysql | + ----------------- + ------------ + ------------------- + --------------------- + 1 row in set (0.00 sec) 2. Modify the mysql configuration file on the server Load balancer and add the following content under the [mysqld] field: server-id = 2 www.2cto.com master-host = 172.16.0.100 master-user = repl master-password = 123456 master-port = 3306 master-connect-retry = 60 replicate-do-db = repl // synchronized database, if you do not write this line, it means to synchronize all databases and then restart the mysql of the slave machine.
On the Server Load balancer, choose mysql> start slave; mysql> show slave status \ G. If the status of Slave_IO_Running and Slave_ SQL _Running is Yes, the setting is successful. Then execute show slave status \ G to display the following: **************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.222 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.20.03 Read_Master_Log_Pos: 98 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 229 rows: log.20.03 rows: Yes Slave_SQ Rochelle running: Yes running: Failed: 0 Last_Error: Skip_Counter: 0 running: 98 Relay_Log_Space: 229 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 running: no Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_S SL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) www.2cto.com Slave_IO_Running: Yes Slave_ SQL _Running: If Yes, the configuration is successful.
5. test whether the Master/Slave server can create a new table on the master server synchronously. mysql> use repl Database changed mysql> create table test (id int, name char (10); Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (1, 'zaq'); Query OK, 1 row affected (0.00 sec) mysql> insert into test values (1, 'xsw '); Query OK, 1 row affected (0.00 sec) mysql> select * from test; + ------ + | id | name | + ------- + ------ + | 1 | zaq | 1 | Xsw | + ------- + ------ + 2 rows in set (0.00 sec) Check whether mysql> use repl; Database changed mysql> select * from test on the slave server; + ------ + | id | name | + ------ + | 1 | zaq | 1 | xsw | + ------ + 2 rows in set (0.00 sec) the configuration is successful. Iv. Problems 1. when you execute The start slave command, The system prompts ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with change master to, and execute show slave status; empty set (0.00 sec) cause: slave has been enabled by default. You need to disable it before enabling the execution of slave stop; then, execute change master to master_host = '100. 16.0.200 ', master_user = 'repl', master_password = '000000', master_log_file = 'Log. 000003 ', master_log_pos = 98; 2. error message: Slave_IO_Running: NO mysql error log information: www.2cto.com Slave I/O: error connecting to master' replication @ 172.16.0.100: 3306 '-retry-time: 60 retries: 86400, Error_code: 1045
Solution 1. Set the logon password when creating the slave remote connection in the primary mysql instance. 2. stop the mysqld service on slave and delete all binary log files from the server, including the master.info file in a data directory and files starting with hostname-relay-bin, then start the mysqld service on slave. Master.info: Records log files, record locations, and connection passwords on the Mysql master server. 3. slave_ SQL _Running: No on slave, prompting that a table is incorrectly defined. Solution: clear and drop all existing tables on the master and slave, and then import new data from the master database. Author ygl23