Master-slave replication of MySQL database _ MySQL

MySQL database master-slave replication bitsCN. the master-slave replication of comMySQL 5.5 Database is studied based on the data on the Internet. The data on the Internet is already very detailed, but I still encountered many problems in practice, the following is a summary based on the information on the Internet and the problems I encountered. System environment: Ubuntu12.04 software version: mysql-server-5.5 host IP: 192.168.0.200 slave IP: 192.168.0.201 operation: 1, host operation: 1), edit mysql configuration file my. cnf [mysqld] server-id = 1 log-bin = mysql-bin note: Some other configurations are available on the Internet, but only these two important configurations are configured for laziness, of course, these two are also required. 2) log on to mysql as the root user and run the following code // Create a user, dean password 123456, and grant the replication slave permission: mysql> grant replication slave on *. * to 'Dean '@' 192. 189.0.201 'identified by '20140901'; // obtain the permission immediately. mysql> flush privileges; // query the file name and status of the binary file (to be used later) mysql> show master status/G File: mysql-bin.000006 Position: 107 Binlog_Do_DB: Binlog_Ignore_DB: 2, slave operation: 1), edit myslq configuration File my. cnf: [mysqld] server-id = 2 // as long as it is different from the host. 2) log on to mysql and enter the following command: a), mysql> c Hange master to master_host = '2017. 168.0.200 ', master_user = 'Dean',/master_password = '000000', master_log_file = 'MySQL-bin.000006 ', master_log_pos = 123456; B), mysql> start slave; c) mysql> show slave status/G if the following error occurs: Slave_IO_Running: Yes Slave_ SQL _Running: Yes, it means no problem. you can create a database on the host and create a table to write test data, then check whether there is any data on the machine. Generally, there is no problem. However, if Slave_IO_Running: Connecting Slave_ SQL _Running: Yes, it indicates that the master/slave service is faulty. in this case, my solution is as follows: 1) directly log on to the mysql server from the host: # mysql-udean-h 192.168.0.200-p123456 if it is rejected, check whether the host's user dean's permissions and firewall settings are correct. (My permissions are rejected) 2) Check permissions. Use root to log on to mysql and select mysql database mysql> use mysql; query the user's access to mysql> select host, user from user; no problem is found from the query data, some dean users accept access from 192.168.0.201. 3) firewall settings allow access from Port 3306 to # sudo ufw allow 3306 and ip address 192.168.0. 201 access is set to allow # sudo ufw allow 192.168.0.201 check status # After sudo ufw status is set, it is still not allowed to directly remotely access the host from the host 4) view port listening # netstat-anpy | grep 3306 finds that the current port 3306 is only listening at 127.0.0.1, and finds the problem. Modify the mysql configuration file my. cnf and annotate the bind-address = 127.0.0.1 in it. check the port listening again and find that the listening is 0.0.0.0: 3306. after testing, remote access is allowed. Run the following command to stop the slave; // disable the slave mysql> reset slave; // reset the slave and continue the operation. The master-slave replication of the Mysql database is completed. In addition, according to the information on the internet, if the host has data before building master-slave replication, you must first lock the host: mysql> flush tables with read lock; then package the data directory, copy it to the slave, and unlock it.
Author: DeanJosonbitsCN.com