Pangkalan data MariaDB dipasang dan dikonfigurasikan berdasarkan replikasi segerak induk-hamba GTID

vim /etc/my.cnf 主从通用配置 binlog-format = mixed #二进制日志记录的模式（高版本默认开启） binlog-checksum = CRC32 #可使主机为写入二进制日志的事件写入校验（高版本默认开启） sync-master-info = 1 #MariaDB依靠操作系统将master.info文件刷新到磁盘。 sync_relay_log_info = 1 #MariaDB依靠操作系统将relay-log.info文件刷新到磁盘。 expire_logs_days = 7 #日志文件过期天数,默认是 0,表示不过期 master-verify-checksum = 1 #主服务器效验 slave-sql-verify-checksum = 1 #从服务器效验

server-id = 56 #MySQL服务器ID,不重复 log-bin = mysql-bin #二进制日志（默认开启） sync-binlog = 1 #主服务器进行设置,用于事务安全 log-bin-index = mysql-bin

server-id = 163 relay-log = relay-bin #中继日志 slave-parallel-threads = 2 #设定从服务器的SQL线程数 #replicate-do-db = renwoleblogdb#复制指定的数据库,多个写多行 replicate-ignore-db = mysql #不备份的数据库,多个写多行 relay_log_recovery = 1 #从站崩溃后可以使用,防止损坏的中继日志处理。 log-slave-updates = 1 #slave将复制事件写进自己的二进制日志 relay-log-index = relay-bin

《复制和二进制日志服务器系统变量》 https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/ 关于系统变量的兼容性，可参阅官方 《MariaDB与MySQL兼容性》 https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/

# mysql -uroot -p Enter password:【输入你的MySQL密码回车】 MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'renwoleuseracc'@'%' IDENTIFIED BY 'renwoleuserpass'; //创建Slave专用备份账号 MariaDB [(none)]> flush privileges; //刷新MySQL权限 MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; //查看授权情况 MariaDB [(none)]> flush tables with read lock; //锁定数据库防止master值变化 MariaDB [(none)]> show master status; //获取master状态值 +-----------------+----------+------------+-----------------+ | File |Position |Binlog_Do_DB|Binlog_Ignore_DB | +-----------------+----------+------------+-----------------+ | mysql-bin.000006| 627 | | | +-----------------+----------+------------+-----------------+ 1 row in set (0.00 sec)

MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.000006", 627); +------------------------------------------+ | BINLOG_GTID_POS('mysql-bin.000006', 627) | +------------------------------------------+ | 0-56-4 | +------------------------------------------+ 1 row in set (0.01 sec)

# mysql -uroot -p Enter password:【输入你的MySQL密码】 MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-56-4"; MariaDB [(none)]> change master to master_host='10.10.10.56',MASTER_PORT = 3306,master_user='renwoleuseracc',master_password='renwoleuserpass',master_use_gtid=slave_pos; //进行主从授权 MariaDB [(none)]> START SLAVE; //启动Slave MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.56 Master_User: renwoleuseracc Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 627 Relay_Log_File: relay.000035 Relay_Log_Pos: 537 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ... ... Using_Gtid: Slave_pos Gtid_IO_Pos: 0-56-4

master_host 表示master授权地址 MASTER_PORT MySQL端口 master_user 表示master授权账号 master_password 表示密码 master_use_gtid GTID变量值

MariaDB [(none)]> unlock tables; //解锁数据表 MariaDB [(none)]> show slave hosts; //查看从服务器连接状态 MariaDB [(none)]> show global status like "rpl%"; //查看客户端

MariaDB [(none)]> show variables like '%relay%';