準備好系統

mysql01 inet addr:192.168.9.142

mysql01 inet addr:192.168.9.143

更新

sam@ubuntu:~$ sudo apt update &;& sudo apt dist-upgrade && sudo apt autoremove

安裝

sam@ubuntu:~$ sudo apt-get install mysql-server mysql-client

sam@ubuntu:~$ sudo vi /etc/mysql/my.cnf

#server 01
[mysqld]
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2
bind-address = 192.168.9.142

#server 02
[mysqld]
server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2
bind-address = 192.168.9.143

建立同步帳號

sam@mysql01:~$ sudo mysql -uroot -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'cp'@'192.168.9.143' IDENTIFIED BY '********';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

測試一下

sam@mysql02:~$ sudo mysql -ucp -p -h 192.168.9.142
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.23-0ubuntu0.16.04.1-log (Ubuntu)

Copyright (c) 2000, 2018, 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> exit;
Bye

mysql01

sam@mysql01:~$ sudo mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql02

sam@mysql02:~$ sudo mysql -uroot -p
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO master_host='192.168.9.142', master_port=3306, master_user='cp', master_password='1qaz@WSX', master_log_file='mysql-bin.000009', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql01

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO master_host='192.168.9.143', master_port=3306, master_user='cp', master_password='1qaz@WSX', master_log_file='mysql-bin.000011', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

建立測試 mysql01

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database my01;
Query OK, 1 row affected (0.00 sec)

察看mysql02

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

確實是有了

再反向做一次

在mysql02建立

mysql> create database my02;
Query OK, 1 row affected (0.00 sec)

察看mysql01

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my01               |
| my02               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

• Prev
• Next