MySQL master-slave backup under redhat

In redhat, MySQL master-slave backup is used as a Java programmer in a small company. In addition to coding, the backup is also very complicated. This time, I have the opportunity to access the master-slave backup of MySQL Data, in the past, the Department had a master-slave relationship, and there was a check list. Even so, there were still a lot of problems in the middle. After a few days of hard work, the problem was solved today, therefore, I decided to write a blog record to help people who encounter similar problems in the future! Prerequisites: Two DB servers, MySQL 5.5.29, are used as one database and backup. Objective: To create a master-slave backup for two databases and implement automatic backup steps: 1. Stop the database application, back up the database in SQL format, and synchronize data to the backup database. 2. edit/etc/my. cnf file, add the following code in mysqld Java code server-id = 1 # To identify the master database. If multiple databases are the master database, this number should be unique log_bin =/var/log/mysql/mysql-bin.log # Make sure this file can be written read-only = 0 # host, you can use binlog-do-db = test # To back up data and write multiple rows of binlog-ignore-db = mysql # databases that do not need to be backed up, write multiple rows 3 and modify my. cnf file, restart the primary database, service mysql restart 4, create a database user for backup, and grant permissions to the slave machine. Here, you can directly authorize and refresh the database permissions, mySQL automatically creates the user's SQL code GRANT REPLICATION SLAVE ON *. * TO 'slave '@' 192. 168.1.101 'identifi Ed by '200'; flush privileges; 5. Modify the slave machine my. cnf file ,, add the following code in the mysqld section and restart the MySQL Xml Code server-id = 2 log_bin =/var/log/mysql/mysql-bin.log replicate-do-db = test # Just copy a database replicate- ignore-db = mysql # do not copy a database 6, set host information on the slave machine, log on TO mysql, and execute the following SQL statement mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST = '2017. 168.1.100 ', MASTER_USER = 'slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin1_xx ', MASTER_LOG_POS = 123456 Mysql> start slave; 7. In the above Code, Master_host is the host address. master_user and pwd are the Server Load balancer users and passwords created on the host, master_log_file and log_pos are very important. If an error is set, the SQL code Got fatal error 1236 from master when reading data from binary log may easily occur: 'Could not find first log file name in binary log index file' 8. To avoid errors, log on to the MySQL host before performing Step 6, execute the following SQL statement to view the host's log file and log pos. Then, when executing the SQL statement in step 6, write the corresponding information to the SQL code show master status; 9, after the preceding steps are completed Yes. You can run the show slave status SQL command on the slave machine to check the slave status. If yes is displayed for both slave_io_running and slave_ SQL _running, it indicates that the slave status is successful! You can perform the update and insert operations on the primary database and verify the operation in the slave database! Ps: the first version was 5.5.29. I used the above method for configuration and failed to complete the configuration. I also found many reasons on the Internet, all of which ended in failure, the configuration is successful only after the database version is replaced with 5.6.10 using the above method. Back up the data first. Run the rpm-qa | grep-I mysql command to view the installed mysql, and then run the rpm-e *** mysql command to uninstall mysql, clear binlog and Error Log Files under/var/lib/mysql, pay attention to edit the mysql-bin.index file after clearing, delete the corresponding information, and then save, during installation, you can configure my. remove the cnf file and overwrite it back after successful installation.