主从数据库
主从数据库
1、概念
Linux中，数据库服务有三种：互为主主，互为主从，一主一从（主从数据库）
互为主主：数据库实时更新
互为主从：数据库达到一定的容量再更新
一主一从：主数据库可同步到从数据库，但从数据库不能同步到主数据库
软件包 mariadb mariadb-server
服务名 mariadb
协议名 mysql
端口号 3306
2、操作
主节点：（master）
#关防火墙和selinux（或配置规则）
[root@master ~]# systemctl stop firewalld
[root@master ~]# setenforce 0
#下载数据库软件包
[root@master ~]# yum -y install mariadb mariadb-server
......
Complete!
#启动数据库服务，并设置开机自启
[root@master ~]# systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
#初始化数据库
[root@master ~]# mysql_secure_installation
Enter current password for root (enter for none): #enter 默认为设置密码
OK, successfully used password, moving on...
Set root password? [Y/n] y #y 设置密码
New password: #数据库密码000000
Re-enter new password:
Password updated successfully!
Remove anonymous users? [Y/n] y #y 移除匿名用户
... Success!
Disallow root login remotely? [Y/n] n #n 不允许root远程登录
... skipping.
Remove test database and access to it? [Y/n] y #y 移除测试数据库
- Dropping test database...
Reload privilege tables now? [Y/n] y #y 重新加载数据库
... Success!
Thanks for using MariaDB!
#修改配置文件
[root@master ~]# vi /etc/my.cnf
[mysqld]
log-bin = mysql-bin #添加 设置为主数据库（也就是以二进制加载日志文件）
server-id = 1 # id号，不跟从数据库一样就行
#重启数据库
[root@master ~]# systemctl restart mariadb
#登录数据库、设置数据库权限
[root@master ~]# mysql -uroot -p000000
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by "000000";
Query OK, 0 rows affected (0.00 sec) #给root用户一个远程登录的权限
MariaDB [(none)]> grant replication slave on *.* to 'zhangsan'@'192.168.130.23' identified by '000000'; #给zhangsan一个从节点复制权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
从节点（slave）：
#下载数据库软件包
[root@slave ~]# yum -y install mariadb mariadb-server
......
Complete!
#启动数据库服务，并设置开机自启
[root@slave ~]# systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
#初始化数据库
[root@slave ~]# mysql_secure_installation
Enter current password for root (enter for none): #enter 默认为设置密码
OK, successfully used password, moving on...
Set root password? [Y/n] y #y 设置密码
New password: #数据库密码000000
Re-enter new password:
Password updated successfully!
Remove anonymous users? [Y/n] y #y 移除匿名用户
... Success!
Disallow root login remotely? [Y/n] n #n 不允许root远程登录
... skipping.
Remove test database and access to it? [Y/n] y #y 移除测试数据库
- Dropping test database...
Reload privilege tables now? [Y/n] y #y 重新加载数据库
... Success!
Thanks for using MariaDB!
#修改配置文件
[root@slave ~]# vi /etc/my.cnf
[mysqld]
server-id = 2 #添加id号
#重启数据库
[root@slave ~]# systemctl restart mariadb
#进入数据库，授权
[root@slave ~]# mysql -uroot -p000000
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '000000';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> change master to master_host='192.168.130.22',master_user='zhangsan',master_password='000000';
Query OK, 0 rows affected (0.032 sec) #连接主数据库，用刚刚复制授权的用户和密码
MariaDB [(none)]> start slave; #启动从节点
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G #查看从数据库状态
......
Master_User: zhangsan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 541
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 840
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #yes
Slave_SQL_Running: Yes #yes
.......
MariaDB [(none)]> exit
Bye
