MySQL主主配置
在虚拟机192.168.1.181和192.168.1.182上的两套zabbix环境，根据搭建的两个环境在上面进行数据库主主配置，为keepalive做准备。
一、目标端drop库
# 目标端drop库
ubuntu@monitoring-slave:~$ mysql -uzabbix -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 303
Server version:5.5.47-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2015, Oracleand/or its affiliates. All rights reserved.
Oracle is a registered trademarkof Oracle Corporation and/or its
affiliates. Other names may betrademarks of their respective
owners.
Type 'help;' or '\h'forhelp. Type '\c' to clearthe current input statement.
mysql> showdatabases;
+--------------------+
|Database |
+--------------------+
|information_schema |
| zabbix |
+--------------------+
2 rows inset (0.00sec)
mysql> dropzabbix
->;
ERROR 1064 (42000): You have anerror in your SQL syntax; check themanual that corresponds to your MySQL server version for the rightsyntax to use near 'zabbix' at line 1
mysql> dropdatabase zabbix
->;
Query OK, 113 rows affected (5.57sec)
mysql> showdatabases;
+--------------------+
|Database |
+--------------------+
|information_schema |
+--------------------+
1 row inset (0.00sec)
# 然后新建一个同名字的库
mysql> createdatabase zabbix character set utf8 collateutf8_bin;
二、跨主机备份
将192.168.1.181上的库复制到192.168.1.182上。
# 授权
ubuntu@monitoring:~$ grantall privileges on zabbix.* to zabbix@192.168.1.181identified by 'zabbix';
#库和库之间的复制，目标端库必须先建立，源端有向目标端读写的权限
ubuntu@monitoring:~$ mysqldump--host=localhost -uzabbix -pzabbix --opt zabbix | mysql--host=192.168.1.182 -uzabbix -pzabbix -C zabbix
三、修改MySQL配置
#查看默认配置文件
mysql --verbose --help | grep -A 1 "Defaultoptions"
#
#修改配置在[mysqld]配置区中加入如下配置。
#
#########################181
[mysqld]
server-id = 11
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
relay-log=mysql-relay
relay-log-index=mysql-relay.index
binlog-do-db=zabbix
replicate-do-db=zabbix
#创建复制用户
mysql> CREATEUSER 'repluser'@'192.168.1.182'IDENTIFIED BY 'zabbix';
Query OK, 0 rows affected (0.06sec)
mysql> GRANTREPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.182'IDENTIFIED BY 'zabbix';
Query OK, 0 rows affected (0.01sec)
mysql> flushprivileges;
Query OK, 0 rows affected (0.02sec)
################182
server-id = 12
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
relay-log=mysql-relay
relay-log-index=mysql-relay.index
#创建复制用户
ubuntu@monitoring-slave:/etc$mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2842
Server version:5.5.47-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2015, Oracleand/or its affiliates. All rights reserved.
Oracle is a registered trademarkof Oracle Corporation and/or its
affiliates. Other names may betrademarks of their respective
owners.
Type 'help;' or '\h'forhelp. Type '\c' to clearthe current input statement.
mysql> CREATEUSER 'repluser'@'192.168.1.181'IDENTIFIED BY 'zabbix';
Query OK, 0 rows affected (0.07sec)
mysql> GRANTREPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.181'IDENTIFIED BY 'zabbix';
Query OK, 0 rows affected (0.01sec)
mysql> flushprivileges;
Query OK, 0 rows affected (0.01sec)
mysql>
四、重启mysql(双机都操作)，使修改的配置生效
cd /etc/init.d
./mysql restart
五、配置双主
# 在192.168.1.181查看mater status
mysql> showmaster status;
+--------------+----------+--------------+------------------+
| File | Position |Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000001| 107 | | |
+--------------+----------+--------------+------------------+
1 row inset (0.00sec)
mysql>
#在192.168.1.182上配置
mysql> changemaster to \
-> master_host='192.168.1.181',
-> master_user='repluser',
-> master_password='zabbix',
-> master_log_file='mysql.000001',
-> master_log_pos=107;
Query OK, 0 rows affected (0.27sec)
mysql> commit;
Query OK, 0 rows affected (0.00sec)
mysql>
同理在181上进行配置。
步骤省略...
六、验证
## 查看181是否都全部同步182的
#查看182的bin日志
mysql> showmaster status \G
*************************** 1. row ***************************
File: mysql.000008
Position: 4929
Binlog_Do_DB: zabbix
Binlog_Ignore_DB:
1 row inset (0.00sec)
mysql> showmaster status ;
+--------------+----------+--------------+------------------+
| File | Position |Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000008| 4929 | zabbix | |
+--------------+----------+--------------+------------------+
1 row inset (0.00sec)
mysql>
#查看181的slave 状态，状态正常
mysql> showslave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master tosend event
Master_Host: 192.168.1.182
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000008
Read_Master_Log_Pos: 4929
Relay_Log_File:mysql-relay.000009
Relay_Log_Pos: 249
Relay_Master_Log_File: mysql.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: zabbix
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 1
Exec_Master_Log_Pos: 4929
Relay_Log_Space: 543
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
1 row inset (0.00sec)
mysql>
## 查看182是否都全部同步181的
#在181上看状态
mysql> showmaster status;
+--------------+----------+--------------+------------------+
| File | Position |Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003| 107 | zabbix | |
+--------------+----------+--------------+------------------+
1 row inset (0.00sec)
mysql>
#看下182的slave
mysql> showslave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master tosend event
Master_Host: 192.168.1.181
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000003
Read_Master_Log_Pos: 107
Relay_Log_File:mysql-relay.000005
Relay_Log_Pos: 249
Relay_Master_Log_File: mysql.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: zabbix
Replicate_Ignore_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: 107
Relay_Log_Space: 543
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row inset (0.00sec)
mysql>
以上状态均正常
七、问题处理
（1）MySQL配置文件绑定127.0.0.1导致3306端口无法对外暴漏
发现在181可以和182 的端口通信，但是182不能telnet 到181，在181的mysql配置文件里面查看是绑定了127.0.0.1 的地址，改成本机ip即可
#
# Instead of skip-networking thedefault is now to listen only on
# localhost which is morecompatible and is not less secure.
#bind-address = 127.0.0.1
bind-address = 192.168.1.181
（2）跳过复制错误问题
刚开始配置的时候182上有一个walle库，因为181上没有创建此库导致同步错误，用如下sql命令跳过复制错误。
#遇到主从复制出错误的情况
stop slave;
set globalsql_slave_skip_counter=1 ;
start slave;
八、其他补充
#撤销主从复制或者主主复制功能
stop slave;
reset slave all;
641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
