mariadb数据库主从复制与读写分离

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日志过期天数，过期则自动清理
read_only=ON ##开启只读模式

[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