架构图
一． 两台机器都要配置
1.配置server-id并开启bin-log功能
[root@linux-node1 ~]# grep '[1]' /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=11
log-bin=/var/lib/mysql/mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
symbolic-links=0
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
2.检查：
MariaDB [(none)]> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]>
3.创建复制账号
MariaDB [(none)]> grant replication slave on . to 'rep'@'192.168.56.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
4.检查创建的rep复制账号
MariaDB [(none)]> select user,host from mysql.user;
+--------+-------------------------+
| user | host |
+--------+-------------------------+
| root | 127.0.0.1 |
| rep | 192.168.56.% |
| root | ::1 |
| | linux-node1.example.com |
| root | linux-node1.example.com |
| | localhost |
| root | localhost |
| zabbix | localhost |
+--------+-------------------------+
8 rows in set (0.00 sec)
二．测试
1.查看位置信息
b11:
MariaDB [db1]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 950 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [db1]>
2.数据库12同步11
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| salt |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.11',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=950;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]>
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 792
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 792
Relay_Log_Space: 825
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 in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]>
4.11同步12数据库
b12:
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [db1]> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.12',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.12
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 825
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 in set (0.00 sec)
ERROR: No query specified
三.配置keepalived
1.b11主节点：
[root@linux-node1 mysql]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
xiaolong.xu@maixunbytes.com
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id master-ha
}
vrrp_instance VI_1 {
state MASTER
interface eth0
mcast_src_ip 192.168.56.11
virtual_router_id 50
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.14/24 dev eth0 label eth0：1
}
}
2.b12从节点：
[root@linux-node2 mysql]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
xiaolong.xu@maixunbytes.com
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id master-ha
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
mcast_src_ip 192.168.56.12
virtual_router_id 50
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.14/24 dev eth0 label eth0：1
}
}
3.在主从节点授权root用户远程登陆
MariaDB [(none)]> grant all on . to root@'192.168.56.%' identified by "123456";
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> select user,host from mysql.user;
+--------+-------------------------+
| user | host |
+--------+-------------------------+
| root | % |
| root | 127.0.0.1 |
| rep | 192.168.56.% |
| root | 192.168.56.% |
| root | ::1 |
| | linux-node1.example.com |
| root | linux-node1.example.com |
| | localhost |
| root | localhost |
| zabbix | localhost |
+--------+-------------------------+
10 rows in set (0.00 sec)
MariaDB [(none)]>
登陆测试
b11主节点创建数据库同步到从节点b12
b11主节点创建数据库同步到从节点b12
a-Z ↩︎