mysql 主从同步

参考:http://blog.csdn.net/dbaxiaosa/article/details/22421969

master配置:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

default-time-zone = ‘+8:00‘


innodb_additional_mem_pool_size = 512M

innodb_buffer_pool_size = 4G

innodb_thread_concurrency = 32

innodb_log_buffer_size = 256M

innodb_flush_log_at_trx_commit = 0


log-bin=mysql-bin

server-id=1121

#binlog-do-db=mysql,maillog,mailmanage

#binlog-ignore-db=syslog,information_schema    #忽略同步binlog

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid


slave配置:


[mysqld]

datadir=/opt/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-bin=mysql-bin

server-id=184

replicate-ignore-db=syslog    ##忽略master上关于syslog库的同步



# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

default-time-zone = ‘+8:00‘


innodb_additional_mem_pool_size = 512M

innodb_buffer_pool_size = 4G

innodb_thread_concurrency = 32

innodb_log_buffer_size = 256M

innodb_flush_log_at_trx_commit = 0


[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

操作步骤:
1,master锁表

mysql> FLUSH TABLES WITH READ LOCK;    ### 锁表

Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 1068735    ##slave同步时使用
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec) 


2,master上mysqldump备份为sql文件

 mysqldump -uroot -p -B maillog(库名)  > /root/maillog.sql

3,master解锁

    mysql>UNLOCK TABLES;

4,master授权

 mysql>GRANT REPLICATION SLAVE ON *.* to ‘账号‘@‘ip‘ identified by ‘密码‘;

5,scp备份的sql文件到slave主机

6,slave导入

  ]#mysql -uroot -p maillog < /root/maillog.sql

7,slave开启同步:

mysql>change master to master_host=‘masterIP‘,master_user=‘授权账号‘,master_password=‘授权密码‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=1068735;

mysql>start slave;

mysql>show slave status\G    ##重点查看以下绿色部分,双Yes

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168....

                  Master_User: mysync

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000009

          Read_Master_Log_Pos: 903745163

               Relay_Log_File: mysqld-relay-bin.000030

                Relay_Log_Pos: 903745308

        Relay_Master_Log_File: mysql-bin.000009

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: syslog

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 903745163

              Relay_Log_Space: 903745507

              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: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

1 row in set (0.00 sec)

说明:
Slave_IO_Running――此进程负责从Slave从Master上读取binlog日志,并写入Slave上的中继日志。
Slave_SQL_Running――此进程负责读取并执行中继日志中的binlog日志。
这两个进程的状态需全部为YES,只要有一个为NO,则复制就会停止。
当Slave中Relay_Master_Log_File和Master_Log_File相同且Read_Master_Log_Pos和Exec_Master_Log_Pos完全相同时,则表明Slave和Master处于完全同步的状态。

8,验证

    master创建一个空库,查看slave能否同步

分别在主从服务器show processlist查看连接,就可以看到授权用户的连接,可证明复制已经生效。

9,监控slave同步异常:

[root@localhost ~]# more /usr/local/bin/check_mysql_slave 

#!/bin/sh

D=`date +%Y%m%d:%T`

declare -a slave_is

slave_is=($(mysql -uroot -p密码 -e "show slave status\G"|grep Running|awk ‘{print $2}‘))

if [ "${slave_is[0]}" = "Yes" -a "${slave_is[1]}" = "Yes" ];then

exit 0

else

mysql -uroot -p密码 -e "stop slave\G"

sleep 2

mysql -uroot -p密码 -e "start slave\G"

###发送短信邮件告警

echo “Slave has error” |mail -s "Subject" -a 附件 -r "发件人" 收件人,收件人

fi




本文出自 “红帽子” 博客,请务必保留此出处http://3404903.blog.51cto.com/3394903/1640186

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。