- mysql同步配置设置：
两台服务器上都安装了mysql数据库，修改配置文件：
vim /etc/my.cnf 该路径为mysql配置文件路径
同步设置需要配置 server_id 和 replicate-do-db属性，并需要配置同步用户；
在“[mysqld]”节下增加需要同步的数据库名称
比如需要同步数据库中的radius库：
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| omc_pm_report |
| performance_schema |
| radius |
| sas |
| sys |
+--------------------+
8 rows in set (0.00 sec)
在my.cnf的[mysqld]节下增加如下配置：
[mysqld]
replicate-do-db=radius
再配置server-id
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 1
根据提示，主备不能使用同一个server-id, 我这里主mysql配置为1，备mysql配置为2，其他值亦可；
到这里，配置文件需要的配置已经配置完。
下边是配置同步用户：
让root用户使用password从任何主机连接到本地mysql服务器
GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;
在mysql命令模式中配置：
[root@localhost ~]# mysql -uroot -p密码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2761
Server version: 5.7.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY '密码' WITH GRANT OPTION;
MySQL [(none)]> flush privileges;
记得使用flush privileges是配置立即生效；
查看root是否配置成功：
MySQL [(none)]> select Host,User from mysql.user;
+-------------+---------------+
| Host | User |
+-------------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-------------+---------------+
7 rows in set (0.00 sec)
MySQL [(none)]>
配置主备关系：
MySQL [(none)]>stop slave;
MySQL [(none)]>reset slave;
MySQL [(none)]>change master to master_host='备机IP',master_user='root',master_password='密码';
MySQL [(none)]>start slave;
备机IP配置对端IP地址即可；
配置完后重启mysql服务
systemctl restart mysql
查看主备状态：
MySQL [radius]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: IP.IP.IP.IP 这里的ip地址应是对端ip地址
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000029
Read_Master_Log_Pos: 22008
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 7945086
Relay_Master_Log_File: mysql-bin.000028
Slave_IO_Running: Yes 检查这三项
Slave_SQL_Running: Yes 检查这三项
Replicate_Do_DB: radius 检查这三项
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
如果所有的状态和上边一样，那么此时，mysql同步功能已经配置成功；
此时不如在主机A的radius库里创建一个jacob_test表，备机B上也是可以查到的：
主机A:
MySQL [radius]> create table test(val int);
Query OK, 0 rows affected (0.13 sec)
主机B:
MySQL [radius]> show tables;
+------------------+
| Tables_in_radius |
+------------------+
| jacob_test |
+------------------+
14 rows in set (0.00 sec)
脚本配置：创建脚本名为：mysqlha.sh的脚本，其中的HA_STATUS判断可忽略
在这里插入代码片[root@localhost mysql]# cat haMysql.sh
#!/bin/bash -
MY_CNF="./my.cnf"
MYSQLD="\[mysqld\]"
HA_STATUS=/usr/lib/eGW/.ha.status
function mysql_ha_replicate_db() {
echo "The function of replicate" >> /var/log/messages
if [ `grep -c "$MYSQLD" $MY_CNF` -ne '0' ];then
echo "The File Has mysqld section" >> /var/log/messages
line=`cat ${MY_CNF} |grep -n "replicate-do-db" |grep -v "\#" |awk -F: '{print $1}' |sed -n
if [ "X${line}" != "X" ]; then
sed -i "${line}c replicate-do-db=radius" $MY_CNF
if [ $? -eq 0 ]; then
echo "$LINENO: set replicate-do-db=radius successfully" >> /var/log/message
else
echo "$LINENO: set replicate-do-db=radius failed" >> /var/log/messages
fi
else
echo "Append modify" >> /var/log/messages
sed -i '/\[mysqld\]/a replicate-do-db=radius' $MY_CNF
if [ $? -eq 0 ]; then
echo "set replicate-do-db=radius successfully" >> /var/log/messages
else
echo "set replicate-do-db=radius failed" >> /var/log/messages
fi
fi
else
echo "The File don't have mysqld section,Append Configuration" >> /var/log/messages
echo "[mysqld]" >> $MY_CNF
echo "replicate-do-db=radius" >> $MY_CNF
fi
}
function mysql_ha_server_id() {
echo "The function of server_id" >> /var/log/messages
local ha_status=$(cat $HA_STATUS)
echo "The server status:$ha_status" >> /var/log/messages
if [[ $ha_status == "MASTER" ]];then
echo "local server is master!" >> /var/log/messages
line=`cat ${MY_CNF} |grep -n "server-id" |grep -v "\#" |awk -F: '{print $1}' |sed -n 1p`
if [ "X${line}" != "X" ]; then
sed -i "${line}c server-id=1" $MY_CNF
if [ $? -eq 0 ]; then
echo "$LINENO: set maseter server-id=1 successfully" >> /var/log/messages
else
echo "$LINENO: set maseter server-id=1 failed" >> /var/log/messages
fi
else
echo "Skip modify" >> /var/log/messages
fi
elif [[ $ha_status == "BACKUP" ]];then
echo "local server is backup!" >> /var/log/messages
line=`cat ${MY_CNF} |grep -n "server-id" |grep -v "\#" |awk -F: '{print $1}' |sed -n 1p`
if [ "X${line}" != "X" ]; then
sed -i "${line}c server-id=2" $MY_CNF
if [ $? -eq 0 ]; then
echo "$LINENO: set backup server-id=2 successfully" >> /var/log/messages
else
echo "$LINENO: set backup server-id=2 failed" >> /var/log/messages
fi
else
echo "Skip modify" >> /var/log/messages
fi
else
echo "Independent deployment" >> /var/log/messages
line=`cat ${MY_CNF} |grep -n "server-id" |grep -v "\#" |awk -F: '{print $1}' |sed -n 1p`
if [ "X${line}" != "X" ]; then
sed -i "${line}c server-id=1" $MY_CNF
if [ $? -ne 0 ]; then
echo "$LINENO: set Independent server-id=1 successfully" >> /var/log/messag
else
echo "$LINENO: set Independent server-id=1 failed" >> /var/log/messages
fi
else
echo "Skip modify" >> /var/log/messages
fi
exit 0
fi
}
function mysql_ha_sql_command() {
echo "The function of sql_command" >> /var/log/messages
HA_CONF=/etc/eGW/ha.conf
local ha_slave_ip=$(awk -F ' = ' '/^slaveip/{print $2}' $HA_CONF)
mysql -uroot -pbaiOMC@123 -e "
GRANT ALL PRIVILEGES ON *.* TO \"root3\"@\"%\" IDENTIFIED BY 'baiOMC@123' WITH GRANT OPTION;
flush privileges;
stop slave;
reset slave;
change master to master_host='$ha_slave_ip',master_user='root',master_password='baiOMC@123';
start slave;
quit"
}
function mysql_ha() {
echo "**********Execute HA mysql function**********" >> /var/log/messages
mysql_ha_replicate_db
mysql_ha_server_id
mysql_ha_sql_command
echo "************End HA mysql function************" >> /var/log/messages
}
mysql_ha
转载注明出处
版权声明：本文为JingJPing原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。