linux MySql 在 Master 主从复制配置

发布时间：2020-12-13 23:29:54 所属栏目：Linux 来源：网络整理

导读：在 M a s t e r 服务器上建立用于 S l a v e 服务器复制数据的帐户 ? ? ? [[email?protected] ~]# mysql ? mysql grant replication slave,replication client on *.* to ‘allentuns‘@‘192.168.2.200‘ identified by ‘[email?protected]@‘; Query OK,0

在 Master 服务器上建立用于 Slave 服务器复制数据的帐户

[[email?protected] ~]# mysql

mysql> grant replication slave,replication client on *.* to ‘allentuns‘@‘192.168.2.200‘ identified by ‘[email?protected]@‘; Query OK,0 rows affected (0.02 sec)

mysql> flush privileges;

Query OK,0 rows affected (0.00 sec)

113

mysql> show grants for ‘allentuns‘@‘192.168.2.200‘; ?#查看授权用户

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

| Grants for [email?protected]??????????????????????????????????????????????????????????????????????????????????????????????????????????????? |

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

| GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘allentuns‘@‘192.168.2.200‘ IDENTIFIED BY PASSWORD ‘*

7B8E3D52A612E2CB04E31B43FCDC20A07317E332‘ |

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

1 row in set (0.00 sec)

?在 Slave 服务器上使用授权用户连接测试

[[email?protected] ~]# mysql -uallentuns [email?protected]@ -h 192.168.2.100

Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 6

Server version: 5.6.12-log MySQL Community Server (GPL)

Copyright (c) 2000,2013,Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement. mysql

修改 Master 服务器上的 Mysqld 主配置文件

[[email?protected] ~]# vim /usr/local/mysql/my.cnf log-bin = master-bin????????????????????????? #二进制日志文件 binlog_format = mixed ?#二进制日志文件索引 server-id = 1???? #用于识别的 ID

port = 3306??????????? #Mysql 的默认端口号 basedir = /usr/local/mysql/ #Mysql 源程序目录 datadir = /mydata/data????? #数据存储目录路径

socket = /var/lib/mysql/mysql.sock #套接字文件路径

innodb_file_per_table = 1??? #每表一个文件

114

[[email?protected] ~]# service mysqld restart

修改 Slave 服务器上的 Mysqld 主配置文件

[[email?protected] ~]# vim /usr/local/mysql/my.cnf

#log-bin = master-bin ?#注释二进制日志文件，如果当其它从服务器的主服务器，否则关闭

#binlog_format = mixed #注释此行

skip_slave_start = 1??? #启动服务时不自动启动从服务线程

read_only = 1?????????? #设置 Slave 服务器为只读

server-id = 10 relay_log = relay_log relay_log_index = relay_log.index port = 3306

basedir = /usr/local/mysql/

datadir = /mydata/data

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

[[email?protected] ~]# service mysqld restart

?查看 Master 服务器的二进制日志及二进制日志事件位置用于 Slave

服务器复制

mysql> RESET MASTER;? ?#清空二进制日志 Query OK,0 rows affected (0.05 sec) mysql> show master status;

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

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

| master-bin.000003 |???? 120 |???????????? |????????????????? |?????????????????? |

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

1 row in set (0.00 sec)

注释：File：表示从此日志开始复制??? Position：表示从这个事件开始复制偏移位

115

?在 Slave 服务器上同步 Master 服务器上面的数据

mysql> CHANGE MASTER TO

-> ?MASTER_HOST=‘192.168.2.100‘,

-> ?MASTER_USER=‘allentuns‘,

-> ?MASTER_PASSWORD=‘[email?protected]@‘,

-> ?MASTER_PORT=3306,

-> ?MASTER_LOG_FILE=‘master-bin.000003‘,

-> ?MASTER_LOG_POS=120;

Query OK,0 rows affected,2 warnings (0.38 sec) mysql> help change master to #获取帮助信息 Name: ‘CHANGE MASTER TO‘

Description: Syntax:

CHANGE MASTER TO option [,option] ... CHANGE MASTER TO MASTER_HOST=‘master2.mycompany.com‘,MASTER_USER=‘replication‘,MASTER_PASSWORD=‘bigs3cret‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘master2-bin.001‘,MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10;

?启动 Slave 服务器的复制线程并查看状态

mysql> start slave;??? #启动 Slave 服务器线程 Query OK,0 rows affected (0.02 sec)

mysql> show slave statusG;

116

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

Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.100? ???????????????????????????????????????????? #Master 服务器地址 Master_User: allentuns??? ? #连接 Master 服务器用户名 Master_Port: 3306???? #Master 服务器的监听端口 Connect_Retry: 60???????????????? ? #重试时间间隔

Master_Log_File: master-bin.000003??? #I/O 线程读取的二进制日志文件 Read_Master_Log_Pos: 120???? ??????????????????????????????????????????????????????? #I/O 线程读取的二进制日志文件事件位置 Relay_Log_File: relay_log.000002????????????????????? #SQL 线程正在读取的中继日志文件 Relay_Log_Pos: 284??? #SQL 线程读取和执行的中继日志文件事件位置 Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes????????????????? #Slave 服务器的 IO 线程状态 Slave_SQL_Running: Yes ???????????????????????????????????????????????????? #Slave 服务器的 SQL 线程状态

Replicate_Do_DB: #下面 Replicate 开头的表示用来指明哪些库或者表在复制时不需要同步 Replicate_Ignore_DB:

Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:

Last_Errno: 0??????????????????? #SQL 线程读取日志参数的错误数量 Last_Error:?????? #SQL 线程读取日志参数的错误消息

Skip_Counter: 0??????????????????? #最近被用于 SQL_SLAVE_SKIP_COUNTER 的值 Exec_Master_Log_Pos: 120

Relay_Log_Space: 451

Until_Condition: None???????????????? #所有原有中继日志的总大小 Until_Log_File:

Until_Log_Pos: 0

117

Master_SSL_CA_File:

Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key:

Seconds_Behind_Master: 0?????????????????? #落后于 Master 服务器的时间 Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error: Last_SQL_Errno: 0

Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1

Master_UUID: 84774a86-3ee8-11e4-a268-000c29ad35d7

Master_Info_File: /mydata/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to u pdate it

Master_Retry_Count: 86400

Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0

ERROR:

No query specified

在 Slave 服务器查看启动的线程

[[email?protected] ~]# mysql -e "show processlist;"

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

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

?在 Master 服务器创建数据库并在 Slave 服务器上验证是否存在

###在 Master 服务器创建数据库并查看

[[email?protected] ~]# mysql -e ‘create database mydbtest;‘ [[email?protected] ~]# mysql -e ‘show databases;‘

| Database?????????? |

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

| information_schema |

| mydbtest?????????? |

| mysql????????????? |

| performance_schema |

| test??????????????? |

###在 Slave 服务器查看是否有‘mydbtest‘数据库

[[email?protected] ~]# mysql -e ‘show databases;‘

| Database?????????? |

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

| information_schema |

| mydbtest?????????? | #数据库已经成功同步到 slave 服务器

| mysql????????????? |

| performance_schema |

| test??????????????? |

?在 Master 与 Slave 服务器查看二进制日志事件位置已更新

###查看 Master 服务器

[[email?protected] ~]# mysql -e ‘show master status;‘

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

###查看 Slave 服务器

[[email?protected] ~]# mysql -e ‘show slave statusG;‘ |grep ‘Read_Master_Log_Pos‘ Read_Master_Log_Pos: 226

5.主从监控

5.1Linux 系统 sendmail 发邮件到 139 外部邮箱

1、安装 sendmail 和 mailx

# yum -y install sendmail mailx

2、修改配置文件

# cp /etc/mail.rc /etc/mail.rc.bak

# cat >> /etc/mail.rc << EOF

set [email?protected] smtp=smtp.139.com

set [email?protected] smtp-auth-password=yi15093547036 smtp- auth=login

EOF

3、重新启动服务

# service sendmail restart

4、发送测试邮件

echo "I Love You" |mail -s "邮件主题:MIS you" [email?protected]

5.2 分别在从服务器(Slave)上创建登陆用户，只限定本地运行，保证安全

mysql> grant all privileges on *.* to "zhengyansheng"@"127.0.0.1" identified by "passwor

Query OK,0 rows affected (0.04 sec)

mysql> grant all privileges on *.* to "zhengyansheng"@"localhost" identified by "passwor d123";

Query OK,0 rows affected (0.00 sec)

5.3Mysql 监控脚本

#!/bin/bash

#check MySQL_Slave Status

#crontab time 00:10

MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ ‘{print $4}‘` MYSQLIP=`ifconfig eth1|grep "inet addr" | awk -F[:" "]+ ‘{print $4}‘` STATUS=$(/usr/local/mysql/bin/mysql -u zhengyansheng -ppassword123 -S /tmp/mysql. sock -e "show slave statusG" | grep -i "running")

IO_env=`echo $STATUS | grep IO | awk ‘ {print $2}‘` SQL_env=`echo $STATUS | grep SQL | awk ‘{print $2}‘` DATA=`date +"%y-%m-%d %H:%M:%S"`

if [ "$MYSQLPORT" == "3306" ]

then

echo "mysql is running" else

mail -s "warn!server: $MYSQLIP mysql is down" [email?protected] fi

if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]

then

echo "Slave is running!" else

echo "####### $DATA #########">>???? /mydata/check_mysql_log/check_mysql_slave.l

echo "Slave is not running!" >>??? /mydata/check_mysql_log/check_mysql_slave.log

echo "Slave is not running!" | mail -s "warn! $MYSQLIP MySQL Slave is not running" 1326

[email?protected] fi

5.4 定时执行监控脚本

[[email?protected] ~]# crontab -l

*/1 * * * * root /bin/sh /mydata/check_mysql_health.sh

5.5 测试：停止 slave 进程，看是否能收到邮件

mysql> stop slave;

Query OK,0 rows affected (0.01 sec)

mysql> show slave statusG

*************************** 1. row *************************** Slave_IO_State:

Master_Host: 192.168.2.100

Master_User: allentuns

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 226

Relay_Log_File: relay_log.000005

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: No #IO 线程已经停止 Slave_SQL_Running: No #SQL 线程已经停止 Replicate_Do_DB:

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

Relay_Log_Space: 615

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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0

Last_IO_Error: Last_SQL_Errno: 0

Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1

Master_Info_File: /mydata/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400

Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0

1 row in set (0.00 sec)

主从半同步复制

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!