在 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;
+-------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_Do_DB | binlog_Ignore_DB | Executed_GtID_Set |
+-------------------+----------+--------------+------------------+-------------------+
| 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 status\G;
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;"
+----+-------------+-----------+------+---------+------+------------------------------------------------------------------------
| ID | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+------------------------------------------------------------------------
| 3 | system user | | NulL | Connect | 533 | Waiting for master to send event | NulL
|
| 4 | system user | | NulL | Connect | 533 | Slave has read all relay log; waiting for the slave I/O thread to update i t | NulL |
| 5 | root | localhost | NulL | query | 0 | init | 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;‘
+-------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_Do_DB | binlog_Ignore_DB | Executed_GtID_Set |
###查看 Slave 服务器
[[email protected] ~]# MysqL -e ‘show slave status\G;‘ |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 status\G" | 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 status\G
*************************** 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)
主从半同步复制
总结以上是内存溢出为你收集整理的linux MySql 在 Master 主从复制配置全部内容，希望文章能够帮你解决linux MySql 在 Master 主从复制配置所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）