master設定：
- 開啟二進制日志
- 授權同步二進制檔案的用戶
slave設定：
- 設定只讀
- 使用change master to將角色轉化為slave
master組態檔添加如下，開啟二進制日志
[mysqld]
server_id=1
log_bin
授權同步二進制檔案的用戶
MariaDB [(none)]>grant replication slave on *.* to 'reliuser'@'10.35.78.%' identified by 'tongbu';
MariaDB [(none)]> show master status; #查看最新binlog串列 # 或者show master logs #查看binlog串列
+-----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 400 | | |
+-----------------------+----------+--------------+------------------+
因為 grant 操作mysql.user表，因此二進制日志也會記錄grant，Position 從開始的245增長到400
MariaDB [(none)]> show binlog events in 'mariadb-bin.000001';
+--------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| mariadb-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.68-MariaDB, Binlog ver: 4 |
| mariadb-bin.000001 | 245 | Query | 1 | 400 | grant replication slave on *.* to 'reliuser'@'10.35.78.%' identified by 'tongbu' |
+--------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
MariaDB [(none)]> select user,password from mysql.user;
……
| | |
| reliuser | *69C3B9CD6B3F5BED3AD2E8030541CD815A668188 |
+----------+-------------------------------------------+
ps：
二進制日志只會記錄寫操作（包括洗掉），查詢什么的不會記錄
二進制日志只會記錄操作成功的，失敗的不會記錄
slave組態檔增添如下：
[mysqld]
server_id=2
read_only=ON #(主到從是單向同步，從資料庫設定只讀，防止資料寫入從服務器，主從資料不一致問題，但是mysql的root仍舊可以寫入)
MariaDB [power]> help change master to;
CHANGE MASTER TO
MASTER_HOST='mysql-1', ###MASTER_HOST盡量不設定ip，使用域名并在主機上設定dns
MASTER_USER='reliuser',
MASTER_PASSWORD='tongbu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=400,
MASTER_CONNECT_RETRY=10;
MariaDB [power]> start slave;
MariaDB [power]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-1
Master_User: reliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 400
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-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: 400
Relay_Log_Space: 827
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: 1
1 row in set (0.00 sec)
--------------------------------------------------------------------master--------------------------------------
MariaDB [(none)]> show processlist;
+----+----------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+----------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 3 | reliuser | mysql-2:57936 | NULL | Binlog Dump | 91 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+----------+---------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
2 rows in set (0.02 sec)
--------------------------------------------------------------------slave--------------------------------------
MariaDB [power]> show processlist;
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 2 | system user | | NULL | Connect | 91 | Waiting for master to send event | NULL | 0.000 |-----> Slave_IO
| 1 | system user | | NULL | Connect | 2103 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |-----> Slave_SQL_Running
| 4 | root | localhost | power | Query | 0 | NULL | show processlist | 0.000 |
+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)
驗證主從復制
master：
MariaDB [(none)]> use test;
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into student values('01' , '趙雷' , '1990-01-01' , '男');
insert into student values('02' , '錢電' , '1990-12-21' , '男');
insert into student values('03' , '孫風' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');
insert into course values('01' , '語文' , '02');
insert into course values('02' , '數學' , '01');
insert into course values('03' , '英語' , '03');
insert into teacher values('01' , '張三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
binlog日志查看與備份策略
mysqlbinlog mariadb-bin.000001 >dump.sql #binlog轉化為可行性的sql陳述句，因此binlog從
某種意義上也是資料庫的一種備份，建議備份在不同的磁盤或磁區，可以在/etc/my.cnf中配置log-bin=/path/
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/240985.html
標籤：其他
下一篇：【spark系列5】spark 3.0.1集成delta 0.7.0原理決議--delta如何進行DDL DML操作以及Catalog plugin API