Configure master/slave Data Synchronization and backup for centos6 mysql5.5
Database A serves as the primary service, and database B serves as the synchronization database (note: the databases on both sides must be one-to-one, or there will be continuous error messages, which means they will not succeed) 1. Configure master vi/etc/my. cnf [mysqld] server-id = 1log-bin = log pathbinlog-do-db = xxxx // indicates that only the xxxx database is backed up, you can configure multiple database binlog-ignore-db = yyy // to ignore the yyy database. Note: If binlog-do-db, if binlog-ignore-db is not added, all databases will be backed up by default. restart Database service mysqld restart and log on to mysql-uroot-p123456 to create a backup account. It is best to have slave, reload, super, grant peplication slave, reload, super on *. * to 'backup '@' % 'identfiend By '200' with grant option; Note: with grant option must be available, indicating that backup has the operational permission to flush privileges; show grant for 'backup '@' % '; // view the user show master status. If the ip address is changed: change master to master_host = 'xxx', master_user = 'backup ', master_password = '000000', master_port = '000000 ', master_log_file = 'master-log.000001 ', master_log_pos = 4, master_connect_retry = 10; 2. Configure slave vi/etc/my. cnf server-id = 2 change master to master_host = 'xxx', Master_user = 'backup ', master_password = '000000', master_port = '000000', master_log_file = 'master-log.000001', master_log_pos = 4, master_connect_retry = 10; Note: log name, username. The password must be the same as master to show slave status \ G. Check whether the information is correct. Use show slave status \ G. You only need to confirm that slave_io_running = yesslave_ SQL _running = yes. I encountered two problems: slave_io_running = connectingslave_ SQL _running = yeslast_io_error: error connecting master ...... after searching for a long time, it turns out that the username and password are incorrect, which is a waste of time. Second: (a bit dizzy) slave_io_running = noslave_ SQL _running = yeslast_io_error: indicates the databases and tables on both sides, the corresponding database and table are not found for synchronization, And then: slave stop; reset slave; // reset the slave change master to master_host = 'xxx', master_user = 'backup ', master_password = '000000', master_port = '000000', master_log_file = 'master-log.000001 ', master_log_pos = 4, master_connect_retry = 10; try again, no way, finally, slave_io_running = yesslave_ SQL _running = yeslast_io_error: null.