mysql 主从

1 更改主数据库配置文件

cat > /etc/my.cnf <<EOF

[mysqld]

server-id=1

log-bin

EOF

2 重启mysql

/etc/init.d/mysqld restart

3 建立复制账号rep

登录mysql

grant replication slave on *.* to 'rep'@'172.16.1.%' identified by '123456';

4 实现对主数据库锁表只读

flush table with read lock;

5 备份，打包

mysqldump -uroot -p123456 --events -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz

6 解锁

unlock tables;

7 从库配置文件

cat > /etc/my.cnf <<EOF

[mysqld]

server-id=2

EOF

8 从库导入数据库

mysql -uroot -p123456 < mysql_bak.2018-07-11.sql

9 配置从库连接主库信息

mysql -uroot -p123456 <<EOF

CHANGE MASTER TO

MASTER_HOST='172.16.1.51',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='db01-bin.000005', #主库执行show master status;

MASTER_LOG_POS=262; #主库执行show master status;

EOF

9 启动mysql主从复制

mysql -uroot -p123456 -e "start slave"

mysql -uroot -p123456 -e "show slave status\G;"

 

常见报错：

1 start slave;

操作后有时候会报错提示 例如从库有这个库 从主库创建同样的库就会提示

Slave_SQL_Running:NO

对于该冲突，解决主从不同步方法1为：

stop slave; 临时停止同步开关

set global sql_slave_skip_counter = 1; 将同步指针指向下一定一个，如果多次不同步，可以重复操作

start slave;

2 Slave_IO_Running:

查看日志：tail -f /application/mysql/data/backup.err

2018-07-13 14:52:24 2149 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal M

ySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be us

ed on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

是配置文件server-id 从库与主库相同

 

 

分享个脚本，可以导出主库的二进制日志的位置并在从库输入

#!/bin/bashIP=`hostname -i`Date=`date +%F`if [ $IP == '172.16.1.51' ]then    rm /root/.ssh/id_dsa* -f    ssh-keygen  -f /root/.ssh/id_dsa -P "" >/dev/null 2>&1    sshpass -p123456 ssh-copy-id -i /root/.ssh/id_dsa.pub "-o StrictHostKeyChecking=no root@172.16.1.41" >/dev/null 2>&1    #/application/mysql/bin/mysql -e "grant replication slave  on *.* to rep@'172.16.1.%' identified by '123456';"    #/application/mysql/bin/mysql -e "flush table with read lock;"    /application/mysql/bin/mysql -e "show master status\G;"| awk -F "[: ]+" 'NR==2 {print $3}' > /tmp/mysql_file.txt    /application/mysql/bin/mysql -e "show master status\G;"| awk -F "[: ]+" 'NR==3 {print $3}' > /tmp/mysql_post.txt    #/application/mysql/bin/mysqldump --events -A -B |gzip > /tmp/mysql-${Date}.sql.gz    scp -rp /tmp/mysql_file.txt  /tmp/mysql-${Date}.sql.gz /tmp/mysql_post.txt 172.16.1.41:/tmp/    /application/mysql/bin/mysql -e "unlock tables;"fiif [ $IP == '172.16.1.41' ]then     while true    do        if [ -e /tmp/mysql-${Date}.sql.gz ]        then            File=`cat /tmp/mysql_file.txt`            Pos=`cat /tmp/mysql_post.txt`            gzip -d /tmp/mysql-${Date}.sql.gz            /application/mysql/bin/mysql