mariadb数据库主从复制:
1,主从服务区节点设置不同的server-id
2,master节点启用二进制日志文件和slave节点开启中继日志文件
3,主节点创建一个拥有复制权限的用户账户
4,查询主节点binlog信息
5,设置从节点同步主节点
master ip:192.168.238.183
slave ip:192.168.238.110
master节点:
[root@master ~]# firewall-cmd --permanent --add-service=mysql [root@master ~]# firewall-cmd --reload [root@master ~]# vi /etc/my.cnf.d/mariadb-server.cnf [mysql] lower_case_tables_names=1 ##表名大小写不敏感 [mariadb] bind-address=192.168.238.183 ##绑定服务ip server_id=183 ##用来表示mariadb的身份,必须唯一(一般用ip地址的最后一组数字表示) log-bin=mariadb-bin ##binlog二进制日志文件 log-basename=master ##binglog的命名规则,binlog会以它为前缀生成日志 binlog-format=mixed max_binlog_size=200M ##生成的log最大值,到达最大值会重新创建一个binlog日志 expire_logs_days=14 ##binlog日志过期天数,过期则自动清理 [root@master ~]# systemctl restart mariadb [root@master ~]# mysqladmin -uroot password '000000' [root@master ~]# mysql -uroot-p000000 MariaDB [(none)]> create user backup@'192.168.238.110' identified by '000000'; MariaDB [(none)]> grant replication slave,reload,super on *.* to backup@'192.168.238.110'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> show master status; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000002 | 960 | | | +--------------------+----------+--------------+------------------+ [root@master ~]# mysqldump -uroot -p000000 --all-databases > back_all [root@master ~]# scp back_all 192.168.238.110:/root
slave节点:
[root@master ~]# vi /etc/my.cnf.d/mariadb-server.cnf [mysql] lower_case_tables_names=1 ##表名大小写不敏感 [mariadb] bind-address=192.168.238.110 ##绑定服务ip server_id=110 ##用来表示mariadb的身份,必须唯一(一般用ip地址的最后一组数字表示) binlog-format=mixed max_binlog_size=200M ##生成的log最大值,到达最大值会重新创建一个binlog日志 expire_logs_days=14 ##binlog日志过期天数,过期则自动清理 [root@master ~]# systemctl restart mariadb [root@master ~]# mysqladmin -uroot password '000000' [root@master ~]# mysql -uroot -p000000 [root@master ~]# mysqldump -uroot -p 新建的数据库 < /root/back_all MariaDB [(none)]> change master to > master_host='192.168.238.110', > master_port=3306, > master_user='backup', > master_password='000000', > master_log_file='mariadb-bin.000002', > emaster_log_pos=960; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.238.183 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 1087 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 684 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes