/* @author:itxm @time:20113-07-24 给从服务器设置权限该用户 grant all on *.* to [email protected] identified by 'passwd'; grant replication slave on *.* [email protected] identifiend by 'pass' 在mysql 配置文件中打开bin-log日志选择项 查看最后一个bin-log 日志 show master status 清空所有的bin-log日志 reset master 刷新日志文件 flush logs 查看bin-log 文件 mysqlbinog --no-defaults mysql-00001.bin; mysql bin-log 备份 mysqldump -uroot -ppassword dbname -l -F > 文件存放路径 (-l 枷锁, -F 刷新bin-log日志) 更具position点恢复数据 mysqlbinlog --no-defaults --start-postion="start" --end-positon="end" 'bin-log日志文件' 主从服务器配置 1.mysql 配置文件 打开 log-bin = mysql-bin server-id = 1 (服务圈内唯一,不同) 2.flush tables with read lock(选做加锁解锁) 可以 mysqldump -uroot -pluwoen test -l -F > d:/test.sql 代替 从服务器配置 1.mysql 配置文件 server-id = 2 master-host = 192.168.16.88 master-user = user master-password = pass master-port = 3306 log-bin = mysql-bin 2.重启服务 查看slave 命令 show slave status 数据库命令 1.start slave 2.stop slave 3.show slave status 4.show master logs (查看所有的binlog日志) 5.change master to 6.show prosslist 数据无法同步 方法二: 1.stop slave 2.set GLOBAL SQL_SLAVE_SKIP_COUNTER =1 3.slave start 方法一: change master to master_host = "192.168.16.88", master_user = 'user', master_password = "pass", master_port = 3306, master_log_file = 'mysql-bin.000004', master_log_pos = 98; 启动slave服务器 Mysql> slave start; show slave status /G change master to master_host='192.168.16.88', master_user='luowen', master_password='luowen', master_log_file='mysql-bin.000001', master_log_pos=107; ---------------------------mysql 5.5.1x以后配置变化---------------------------------------- 主配置不变,依旧是 server-id=1 log-bin=log binlog-do-db=database1 //需要同步的数据库 binlog-do-db=database2 binlog-ignore-db=mysql //被忽略的数据库 从配置改为: server-id=2 #master-host=192.168.16.88 #master-user= luowen #master-password= luowen #master-port=3306 #master-connect-retry=60 replicate-do-db=database1 //同步的数据库 replicate-do-db=database2 replicate-ignore-db=mysql //被忽略的数据库 这也提示了我们需要使用change master to 即: mysql>change master to >master_host='192.168.16.88', >master_user='luowen', >master_password='luowen', >master_log_file='bin-log.00001', >master_log_pos=107; 然后start slave;
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/4579.html