mysql 主从复制

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 statusG;"

常见报错：
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/bash
IP=`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 statusG;"| awk -F "[: ]+" 'NR==2 {print $3}' > /tmp/mysql_file.txt
    /application/mysql/bin/mysql -e "show master statusG;"| 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;"
fi

if [ $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 </tmp/mysql-${Date}.sql
            /application/mysql/bin/mysql<< EOF
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='$File',
MASTER_LOG_POS=$Pos;
EOF
            /application/mysql/bin/mysql -e "start slave;"
            /application/mysql/bin/mysql -e "show slave statusG;"|egrep "IO_Running|SQL_Running|Seconds_Behind_Master"|sed -n '1,3p'
        fi
        exit
    done
fi