主从数据库

目录

• 主从数据库
  • 1、概念
  • 2、操作

主从数据库

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