mysql 创建主从数据库_MYSQL---基于mysql多实例数据库创建主从复制

(一)MASTER主库

1. MASTER主库操作设置

[root@db02 data]# mysql -uroot -p -S  /data/3306/mysql.sock

Enter password:                                                  #登陆主库3306

mysql> show variables like 'server_id'；

mysql> show variables like 'log_bin';                   #binlog已经开启

2.添加rep@10.0.0.%用户并授权从库访问权限

mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';

mysql> select user.host from mysql.user；

mysql> show grants for rep@'10.0.0.%';

3.查看当前主库状态

mysql> show master status;

4.跨服务器情况下需要锁表操作，

mysql> flush table with read lock;        #锁表操作

mysql> unlock tables;                          #解锁操作

默认解锁时间参考一下信息

mysql> show status like "%timeout%";

+------------------------------+-----------+

| Variable_name              | Value     |

+------------------------------+-----------+

| interactive_timeout        | 28800    |

| wait_timeout                  | 28800    |

+------------------------------+-----------+

5.锁表后进行数据文件迁移(大于50G需要停库打包迁移)

[root@db02 ~]#  mkdir /server/backup -P

[root@db02 ~]#  mysqldump -uroot -p'123456' -S /data/3306/mysql.sock --event -A -B -x --master-data=1|gzip >/server/backup/mysql_bak.$(date +%F).sql.gz

[root@db02 backup]# ls -l

-rw-r--r-- 1 root root 144406 Sep  3 16:03 mysql_bak.2016-09-03.sql.gz

#注意： -A 表示备份所有库 -B 表示增加use DB 和drop等(导库时会直接覆盖原有的库  -x 和 --master-data=1参数作用为在从库执行change master to语句内无需binlog文件及对应位置点)

#记录下maser的信息状态，以便在从库上操作

mysql> show master status;

6.跨服务器在从库内导入主库mysqldump出的数据

[root@db02 ~]# cd  /server/backup

[root@db02 backup]# gzip -d mysql_bak.2016-09-03.sql.gz  #解压后删除源文件

[root@db02 backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock

(----------------------------后跟slave从库操作------------------------------)

(二)SLAVE从库

1.从库配置复制参数，生成master.info文件

[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock << EOF

CHANGE MASTER TO

MASTER_HOST='10.0.0.52',                             #主库IP地址

MASTER_PORT=3306,                                     #主库端口

MASTER_USER='rep',                                      #主库建立用于复制的账号

MASTER_PASSWORD='123456',                    #主库建立账号的密码

MASTER_LOG_FILE='mysql-bin.000002',        #master status二进制文件

MASTER_LOG_POS=735;                                #master status日志偏移量

EOF

文件在/data/3307/data目录文件下

[root@db02 data]# find /data -type f -name "master.info"

/data/3307/data/master.info

2.启动从库同步开关，测试主从复制配置

[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "start slave"                                                    #开启同步命令

[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G                                       #查看开启状态

主从复制是否成功，最关键的三项状态参数：

[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Running|Behind_Master"

MYSQL主从复制应用技巧实践

1. 从库停止复制故障案例

show slave status：报错且状态为：

Slave_IO_Running: Yes

Slave_SQL_Running:No

Seconds_Behind_Master: NULL

解决办法一：

mysql> slave stop;

mysql> set global sql_slave_skip_counter=n;        #忽略执行N个更新

mysql> slave start;

解决办法二：

[root@db02 ~]#  grep slave-skip /data/3306/my.cnf

slave-skip-errors = 1032.,1062,1007

2.让mysql从库记录binlog日志的方法

在从库my.cnf中加入以下参数重启服务生效即可

log-slave-updates                       #必须要有这个参数

log-bin = /data/3307/mysql-bin

expire_log_days = 7                   #等于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 |xargs rm -f

3.主从复制延迟问题的原因及解决方案

问题一.  从库太多，导致复制延迟

问题二.  从库硬件比主库差，导致复制延迟

问题三.  慢SQL语句过多

问题四.  主从复制的设计问题

问题五.  主从库之间的网络延迟

问题六.  主库读写压力大，导致复制延迟