构建高性能的MYSQL数据库系统-主从复制

转载

mb5ff2f2755a961 2021-08-23 16:09:00

文章标签 mysql sql 服务器数据库 vim 文章分类 代码人生

构建高性能的MYSQL数据库系统-主从复制

实验环境：

DB1:172.16.1.100

DB2:172.16.1.101

VRRIP:172.16.1.99

步骤：

yum -y install mysql

1。修改DB1的mysql配置文件

1 2 3 4 5	`server-id = 2 #DB1和DB2的id 必须不同` `log-bin=mysql-bin #开启mysql的二进制日志功能<``br``>read_only=1 #定义主从复制，只读模式` `relay-log = mysql-relay-bin #定义中继日志的命名格式` `replicate-wild-ignore-table=mysql.% #不复制的数据库或表` `replicate-wild-ignore-table=test.% #不复制的数据库或表<``br``>replicate-wild-ignore-table=information_schema.% #不复制的数据库或表`

2. 在DB1创建复制用户并授权

1	`grant replication slave on . to "chen"@"172.16.1.101" identified by "123456";<``br``>show master status;<``br``>mysql> show master status;<``br``>+------------------+----------+--------------+------------------+<``br``>\| File \| Position \| Binlog_Do_DB \| Binlog_Ignore_DB \|<``br``>+------------------+----------+--------------+------------------+<``br``>\| mysql-bin.000006 \| 106 \| \|　　　　　　　　　 \|<``br``>+------------------+----------+--------------+------------------+<``br``>1 row in set (0.00 sec)`

3.在DB2的mysql库将DB1设为自己的主服务器

1 2 3 4 5 6	`mysql > change master to \` `master_host="172.16.1.100",` `master_user="chen",` `master_password="123456",` `master_log_file="mysql-bin.000006",` `master_log_pos=106;`

4.然后运行

1	`mysql > start slave;`

5.查询DB2运行状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45	`mysql > show slave status;` `mysql> show slave status\G;` `************************* 1. row *************************` `Slave_IO_State: Waiting for master to send event` `Master_Host: 172.16.1.100` `Master_User: chen` `Master_Port: 3306` `Connect_Retry: 60` `Master_Log_File: mysql-bin.000009` `Read_Master_Log_Pos: 106` `Relay_Log_File: mysql-relay-bin.000021` `Relay_Log_Pos: 251` `Relay_Master_Log_File: mysql-bin.000009` `Slave_IO_Running: Yes` `Slave_SQL_Running: Yes` `Replicate_Do_DB:` `Replicate_Ignore_DB:` `Replicate_Do_Table:` `Replicate_Ignore_Table:` `Replicate_Wild_Do_Table:` `Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%` `Last_Errno: 0` `Last_Error:` `Skip_Counter: 0` `Exec_Master_Log_Pos: 106` `Relay_Log_Space: 551` `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)` `ERROR:` `No query specified`

6.然后再DB1和DB2配置上面的配置

7.在DB1和DB2上下载keepalived

yum install keepalived

8.配置keepalived的配置文件

[root@db1 ~]# vim /etc/keepalived/keepalived.conf

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49	`[root@db2 ~]# vim /etc/keepalived/keepalived.conf` `auth_type PASS` `auth_pass 1111` `}` `virtual_ipaddress {` `172.16.1.99` `}` `}` `virtual_server 172.16.1.99 3306 {` `delay_loop 2` `#lb_algo rr #LVS算法，用不到，我们就关闭了` `#lb_kind DR #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL` `persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器` `protocol TCP` `real_server 172.16.1.101 3306 { #检测本地mysql，backup也要写检测本地mysql` `weight 3` `notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死` `keepalived实现切换` `TCP_CHECK {` `connect_timeout 3 #连接超时` `nb_get_retry 3 #重试次数` `delay_before_retry 3 #重试间隔时间` `}` `}` `"/etc/keepalived/keepalived.conf" 40L, 1292C written` `[root@db2 ~]# vim /etc/keepalived/keepalived.conf` `auth_type PASS` `auth_pass 1111` `}` `virtual_ipaddress {` `172.16.1.99` `}` `}` `virtual_server 172.16.1.99 3306 {` `delay_loop 2` `#lb_algo rr #LVS算法，用不到，我们就关闭了` `#lb_kind DR #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL` `persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器` `protocol TCP` `real_server 172.16.1.101 3306 { #检测本地mysql，backup也要写检测本地mysql` `weight 3` `notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死` `keepalived实现切换` `TCP_CHECK {` `connect_timeout 3 #连接超时` `nb_get_retry 3 #重试次数` `delay_before_retry 3 #重试间隔时间` `}` `}`

8./usr/local/keepalived/mysql.sh

构建高性能的MYSQL数据库系统-主从复制_sql

#!/bin/bash
#function:监视mysql主从服务器状态
#written by:alec
#
user=mysql
host=172.16.1.150
password=123
#
check_mysql() {
IO=`/usr/local/mysql/bin/mysql -u$user -p$password -h$host -e 'show slave status\G'|grep IO_Running|tr -s ' '|awk '{print $2}'|grep Yes|wc -l`
SQL=`/usr/local/mysql/bin/mysql -u$user -p$password -h$host -e 'show slave status\G'|grep SQL_Running|tr -s ' '|sed -n '1p'|awk '{print $2}'|grep Yes|wc -l`
if [ "$IO" -eq 1 ]&&[ "$SQL" -eq 1 ]
then
service keepalived status|grep pid
if [ $? -eq 0 ]
then
echo "服务已经启动"
else
service keepalived start
if [ $? -eq 0 ]
then
echo "服务启动成功"
else
echo "服务启动失败"
fi
fi
else
service keepalived status|grep pid
if [ $? -eq 0 ]
then
service keepalived stop
if [ $? -eq 0 ]
then
echo "服务关闭成功"
else
echo "服务关闭失败"
fi
else
echo "服务已经关闭"
fi
fi
}
main(){
check_mysql
}
main;