查看master的运行情况:[root@master] mysql -uroot -p************[root@master] mysql> show master status \g; *************************** 1. row *************************** file: mysql-bin.000014 //这个信息点要记住，下面用 position: 170017372 //这个信息点要记住，下面用 binlog_do_db: ipharmacare_admin binlog_ignore_db: mysql,information_schema,performance_schema executed_gtid_set: 1 row in set (0.00 sec)
查看slave的运行情况:[root@slave] mysql -uroot -p************[root@slave] mysql> show slave status \g; *************************** 1. row *************************** slave_io_state: master_host: master.mysql.ipharmacare.org master_user: slave master_port: 3306 connect_retry: 60 master_log_file: mysql-bin.000013 read_master_log_pos: 1003623481 relay_log_file: mysql-bin.000022 relay_log_pos: 36726417 relay_master_log_file: mysql-bin.000013 slave_io_running: no slave_sql_running: no replicate_do_db: ipharmacare_admin replicate_ignore_db: mysql,information_schema,performance_schema replicate_do_table: replicate_ignore_table: ipharmacare_admin.tb_hospital,ipharmacare_admin.t_customer,ipharmacare_admin.t_license,ipharmacare_admin.tb_hospital_zone_license,ipharmacare_admin.tb_hospital_license replicate_wild_do_table: ipharmacare_admin.% replicate_wild_ignore_table: last_errno: 0 last_error: skip_counter: 0 exec_master_log_pos: 1003623481 relay_log_space: 1003624042 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_ssl_cipher: master_ssl_key: seconds_behind_master: null master_ssl_verify_server_cert: no last_io_errno: 0 last_io_error: last_sql_errno: 0 last_sql_error: replicate_ignore_server_ids: master_server_id: 1 master_uuid: a8ddc479-8862-11e2-b6df-2761731e3dd6 master_info_file: /mnt/mysql/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: master_retry_count: 86400 master_bind: last_io_error_timestamp: last_sql_error_timestamp: master_ssl_crl: master_ssl_crlpath: retrieved_gtid_set: executed_gtid_set: auto_position: 0 replicate_rewrite_db: channel_name: master_tls_version: 1 row in set (0.00 sec)
结论:由上可知数据同步延迟很多,且希望重新做主从,使得主从在数据上保持完全同步.
先进入主库，进行锁表，防止数据写入[root@master] mysql> flush tables with read lock;
进行master数据备份[root@master] cd /mnt/mysql/bakdata[root@master] mkdir baksql[root@master] cd baksql[root@master] mysqldump ipharmacare_admin -uroot -p****** --opt> ipharmacare_admin.sql或者:mysqldump -uroot -p***** --default-character-set=utf8 ipharmacare_admin > ipharmacare_admin.sql
打包数据(可选)[root@master] 7za a ipharmacare_admin_20160505.7z ipharmacare_admin.sql
把mysql备份文件传到从库机器，进行数据恢复[root@slave] cd /usr/downloads/[root@slave] scp root@master:/mnt/mysql/bakdata/ipharmacare_admin_20160505.7z ./[root@slave] 7az x ipharmacare_admin_20160505.7z [root@slave] mysql -uroot -p*****;[root@slave] mysql> drop database ipharmacare_admin;[root@slave] mysql> create database ipharmacare_admin default character set utf8 collate utf8_general_ci;[root@slave] msyql> source baksql.sql;
更新/设置同步进度点 [root@slave] change master to master_host='master.mysql.ipharmacare.org', master_user='slave', master_port=3306, master_password='************', master_log_file='mysql-bin.000014', master_log_pos=170017372;
注意:
1) 做了mysql主从复制以后，使用mysqldump对数据备份时，一定要注意按照如下方式： [root@master] mysqldump –master-data –single-transaction –user=username –password=password dbname> dumpfilename这样就可以保留file和position的信息，在新搭建一个slave的时候，还原完数据库，file和position的信息也随之更新，接着再start slave 就可以很迅速的完成增量同步。2) 忘记主从复制时,对从库用户密码时,可以这样去重置: [root@master] grant replication slave on *.* to 'slave'@'slave.mysql.ipharmacare.org' identified by 'slave';