Centos 7系統 mysql主主要資料同步，centosmysql

Centos 7系統 mysql主主要資料同步，centosmysql

準備工作

主伺服器 IP 192.168.2.225

從伺服器 IP 192.168.2.168

Mysql主伺服器

1、配置防火牆允許3306/tcp連接埠

[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

2、關閉selinux #selinux是Linux中的安全

[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Disabled
3、修改/etc/my.cnf設定檔

[root@localhost ~]# vim /etc/my.cnf

server-id       = 1    #伺服器的ID，必須唯一log-bin=mysql_bin    #開啟二進位日誌功能，名字可以隨便起，最好有含義binlog_cache_size=1M  #為每個session分配的記憶體，在事務過程中用來儲存二進位的緩衝binlog_format=mixed  #主從複製的格式化（mixed，statement，row，預設格式是 statement）expire_logs_days=7   #二進位日誌自動刪除/到期的天數。預設值為0，表示不自動刪除。slave_skip_errors=1062  #跳過主從複製中遇到的所有錯誤或指定類型的錯誤，避免slave端服務終端。如：1062錯誤是指一些主鍵

重複,1032 錯誤是因為主從資料庫資料不一致

log_slave_updates=1  log_slave_update 表示 slave 將複製事件寫進自己的二進位日誌

relay-log-index = slave-relay-bin.index   #作為從伺服器時的中繼日誌
auto-increment-increment = 2   # 自增因子（每次加2）
auto-increment-offset = 1   # 自增位移（從1開始），單數

[root@localhost ~]# systemctl restart mysqld

4、修改mysql登入密碼

[root@localhost ~]# mysqladmin -u root password '123'
You have new mail in /var/spool/mail/root
[root@localhost ~]# mysql -u root -p    #登入mysql

5、給從伺服器授權帳號

mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123456';  #給從伺服器授權帳號

mysql> flush privileges;   #重新整理日誌
mysql> show master status;       #查看File列顯示日誌名，position列顯示位移量，這兩個值在後面配置從伺服器的時候需要。

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000002 |      507 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

6、配置同步

mysql> change master to master_host='192.168.2.168',master_user='slave',master_password='123456',master_log_file='mysql_bin.000007',master_log_pos=585;  #按從伺服器結果更改上面命令中的master_log_file和master_log_pos參數

mysql> start slave;          #啟動slave服務   

mysql> show slave status\G;      #查看Slave狀態，確保以下兩個值為YES。   

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

7、驗證主從伺服器資料同步（包含同步1資料庫、2表、3、記錄：記錄表示表中的資料）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.03 sec)

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

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

mysql> use lxy;

mysql> create table user (id int(10) not null,name char(20) default '', primary key (id));
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+---------------+
| Tables_in_lxy |
+---------------+
| user          |
+---------------+
1 row in set (0.01 sec)
mysql> insert into user values (1,'wo shi zhu server');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | wo shi zhu server |
+----+-------------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | wo shi zhu server  |
|  2 | wo shi cong server |
+----+--------------------+
2 rows in set (0.00 sec)

Mysql從伺服器

1、配置防火牆允許3306/tcp連接埠

[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

2、關閉selinux #selinux是Linux中的安全

[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Disabled

3、修改/etc/my.cnf設定檔

[root@localhost ~]# vim /etc/my.cnf

server-id       = 2relay-log = relay-binrelay-log-index = slave-relay-bin.index

binlog_format=mixed

binlog_cache_size=1Mexpire_logs_days=7slave_skip_errors=1062log_slave_updates=1auto_increment_increment=2 #ID自增從2開始，雙數auto_increment_offset=2

4、修改mysql登入密碼

[root@localhost ~]# mysqladmin -u root -p password '123'
Enter password:
[root@localhost ~]# mysql -u root -p    #登入mysql

5、配置同步

mysql> change master to   master_host='192.168.2.225',master_user='slave',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=507；    #按主伺服器結果更改上面命令中的master_log_file和master_log_pos參數

mysql> start slave;            #啟動slave服務

mysql> show slave status\G;         #查看Slave狀態，確保以下兩個值為YES。   

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

6、給主伺服器授權帳號

mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123456';  #給主伺服器授權帳號

mysql> flush privileges;   #重新整理日誌
mysql> show master status;    #查看File列顯示日誌名，position列顯示位移量，這兩個值在後面配置主伺服器的時候需要。
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000007 |      585 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

7、驗證主從伺服器資料同步（包含同步1資料庫、2表、3、記錄：記錄表示表中的資料）

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

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

mysql> use lxy;
Database changed
mysql> show tables;
+---------------+
| Tables_in_lxy |
+---------------+
| user          |
+---------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | wo shi zhu server |
+----+-------------------+
1 row in set (0.00 sec)

mysql> insert into user values (2,'wo shi cong server');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | wo shi zhu server  |
|  2 | wo shi cong server |
+----+--------------------+
2 rows in set (0.00 sec)