一、MySQL的主從複製
注意事項：
主節點必須開啟二進位制日誌，在從節點上務必開啟中繼日誌；
中繼日誌：從伺服器上記錄下來從主伺服器的二進位制日誌檔案同步過來的事件；
下面來做一個小的實驗，兩個空的資料庫的主從複製
配置前注意事項：務必同步時間 ntpdate 172.18.0.1
如果服務開啟先停止服務，清空資料
systemctl stop mariadb
rm -rf /var/lib/mysql/*
實驗演示：
1、主節點上的配置檔案中定義如下
[server] #在配置檔案中找到[server]配置段定義 skip_name_resolve = on #為了方便跳過地址解析 innodb_file_per_table = on max_connections = 20000 #上述這些配置可以不用一定配置，但是建議可以寫上 log_bin = master-log #最主要的兩項配置 server_id = 1 read_only=ON #預設只讀是開啟的，可以不用設定
2、從節點上的配置
[server] skip_name_resolve = on innodb_file_per_table = on max_connections = 20000 relay_log = relay-log #啟動中繼日誌 server_id = 2 #這裡的節點就不能為1，這是從節點
3、主節點上授權一個使用者可以做複製操作
MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.18.77.%' identified by 'centos'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position| Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000003 | 495 | | +-------------------+----------+--------------+------------------+
4、然後可以讓從伺服器來複制
設定從主伺服器的哪個檔案的哪個位置開始啟動複製，要設定從伺服器指的主伺服器要使用下述的命令啟動複製
MariaDB [(none)]> change master to master_host='172.18.77.7',master_user='repluser',master_password='centos',master_log_file='master-log.000003',master_log_pos=495; MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.18.77.7 -----------------> Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-log.000003 -----------------> Read_Master_Log_Pos: 495 Relay_Log_File: relay-log.000001 -----------------> Relay_Log_Pos: 4 Relay_Master_Log_File: master-log.000003 Slave_IO_Running: No -----------------> 這裡為no表示複製還沒有開啟 Slave_SQL_Running: No -----------------> 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: 495 Relay_Log_Space: 245 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: NULL ------------------> 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: 0
“---------------->” 表示需要注意的點
現在可以開啟複製功能
MariaDB [(none)]> start slave io_thread,sql_thread; MariaDB [(none)]> show slave status\G;
上述已經完成了主從複製
5、測試：
MariaDB [(none)]> create database mydb; 在主節點上建立一個表 MariaDB [(none)]> show databases; 在從節點檢視這個表是否已經複製過來 +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+
二、MySQL的主主複製
主主複製：
互為主從：兩個節點各自都要開啟binlog和relay log；
1、資料不一致；
2、自動增長id；
定義一個節點使用奇數id
auto_increment_offset=1
uto_increment_increment=2
另一個節點使用偶數id
auto_increment_offset=2 id遞增使用偶數
auto_increment_increment=2 id增長步進為2
配置：
1、server_id必須要使用不同值；
2、均啟用binlog和relay log；
3、存在自動增長id的表，為了使得id不相沖突，需要定義其自動增長方式；
服務啟動後執行如下兩步：
4、都授權有複製許可權的使用者賬號；
5、各把對方指定為主節點；
複製時應該注意的問題：
1、從服務設定為“只讀”；
在從伺服器啟動read_only，但僅對非SUPER許可權的使用者有效；
阻止所有使用者：
mysql> FLUSH TABLES WITH READ LOCK;
2、儘量確保複製時的事務安全
在master節點啟用引數：
sync_binlog = ON
如果用到的是InnoDB儲存引擎：
innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON
3、從伺服器意外中止時儘量避免自動啟動複製執行緒
4、從節點：設定引數
sync_master_info=ON
sync_relay_log_info=ON
實驗演示：
環境準備
兩臺主機 節點1： 172.18.77.7
節點2： 172.18.77.77
1、實驗前先將mysql服務停止修改配置檔案
在節點1上的配置如下
[root@bixia ~]#vim /etc/my.cnf.d/server.cnf [server] log_bin = master-log relay_log = relay-log server_id = 1 -----------> 兩臺主機的server_id必須有一個為主 auto_increment_offset=1 auto_increment_increment=2
在節點2上的配置如下
[root@huangshang ~]#vim /etc/my.cnf.d/server.cnf [server] log-bin = master-log relay_log = relay-log server_id = 2 -----------> auto_increment_offset=2 -----------> auto_increment_increment=2
2、檢視兩臺主機各自處於二進位制日誌的哪個位置
[root@bixia ~]#mysql -pcentos MariaDB [(none)]> show master status; #節點1上的二進位制日誌所處的位置 +-------------------+----------+--------------+------------------+ | File | Position| Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000003 | 245 | | | +-------------------+----------+--------------+------------------+
[root@huangshang ~]#mysql MariaDB [(none)]> show master status; #節點2上的二進位制日誌所處的位置 +-------------------+----------+--------------+------------------+ | File | Position| Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000001 | 245 | | | +-------------------+----------+--------------+------------------+
3、都授權有複製許可權的使用者賬號
兩個節點都要授權
[root@bixia ~]#mysql #有密碼用-p加密碼就可以 也可用-u指明使用者 MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.18.77.%' identified by 'centos';
4、兩個節點各把對方指定為主節點；並且指定從對方的哪個二進位制日誌檔案的什麼位置開始複製
第一個節點1
change master to master_host='172.18.77.77',master_user='repluser',master_password='centos',master_log_file='master-log.000001',master_log_pos=245; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G; #檢查是否已經複製開啟 Slave_IO_Running: Yes Slave_SQL_Running: Yes
第二個節點2
MariaDB [(none)]> change master to master_host='172.18.77.7',master_user='repluser',master_password='centos',master_log_file='master-log.000003',master_log_pos=245; MariaDB [(none)]> show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes
5、測試：
(1) 先在172.18.77.7這個節點上刪除一行資料，在172.18.77.77這個節點上檢視是否已經同步
MariaDB [hidb]> delete from students where id=998;
另一個節點上172.18.77.77檢視 （沒有998這行資料已經證明資料同步）
(2) 反過來測試：先在172.18.77.77這個節點上修改一行資料，在172.18.77.7這個節點上檢視是否已經同步
MariaDB [hidb]> update students set major='tiantian' where id=995; 在172.18.77.7上檢視 MariaDB [hidb]> select * from students; ......... | 995 | stu995 | 39 | F | tiantian |
(3) 插入一些資料檢查id是否的遞增（172.18.77.7）； 我們在配置檔案中定義的是以奇數遞增的id號
MariaDB [hidb]> insert into students (name,age,gender,major) values ('longmu',45,'F','qilongpenhuo'),('xiao emo',40,'F','wenxue')
MariaDB [hidb]> select * from students;
.....
| 1003 | longmu | 45 | F | qilongpenhuo |
| 1005 | xiao emo | 40 | F | wenxue |
+------+----------+------+--------+----------------------------------+在另一臺節點上檢視也是同步的
但是在另一臺主機上（172.18.77.77）插入資料時顯示如下
MariaDB [hidb]> insert into students (name,age,gender,major) values ('zhoutou',76,'F','tangtou'),('yuqian',40,'M','hejiu');
MariaDB [hidb]> select * from students;
......
| 1003 | longmu | 45 | F | qilongpenhuo |
| 1005 | xiao emo | 40 | F | wenxue |
| 1006 | zhoutou | 76 v | F | tangtou |
| 1008 | yuqian | 40 | M | hejiu |
+------+----------+------+--------+----------------------------------------------+實驗證明id號的遞增並不是從其中插入從1004開始的；這裡希望可以注意一下