如何部署主从数据库
1.规划节点
IP | 主机名 |
---|---|
192.168.200.10 | mysql1 (主) |
IP | 主机名 |
192.168.200.20 | mysql2(从) |
2.基础准备
使用本地PC环境的VMWare Workstation软件进行实操练习,镜像使用CentOS-7-x86_64-DVD-1511.iso。虚拟机配置为1核/2G内存/20G硬盘。
- 安装两台虚拟机,配置ip,yum源
- 修改主机名(同2)
[root@localhost ~]# hostnamectl set-hostname mysql1 (修改主机名)
[root@localhost ~]# bash (刷新shell命令行)
[root@mysql1 ~]# hostnamectl (查看主机信息)
[root@mysql1 ~]# hostnamectl
Static hostname: mysql1
Icon name: computer-vm
Chassis: vm
Machine ID: d52c665a53624c61901757aefc7b5f6d
Boot ID: d135a5598ddd4917bcc623917a016808
Virtualization: vmware
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-327.el7.x86_64
Architecture: x86-64
- 关闭防火墙和SELinux服务(同2)
[root@mysql1 ~]# setenforce 0 (设置关闭 1开启0关闭)
setenforce: SELinux is disabled
[root@mysql1 ~]# systemctl stop firewalld (关闭防火墙)
- 配置/etc/hosts文件(同2)
[root@mysql1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.10 mysql1
192.168.200.20 mysql2
5.安装数据库服务(同2)
[root@mysql1 ~]# yum install -y mariadb mariadb-server(安装数据库以及服务)
- 启动数据库服务并设置开机自启(同2)
[root@mysql1 ~]# systemctl start mariadb (启动数据库)
[root@mysql1 ~]# systemctl enable mariadb (设置开机自启)
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
7.初始化数据库(同2)
[root@mysql1 ~]# mysql_secure_installation(初始化数据库)
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
Enter current password for root (enter for none): (提示未创建密码,默认回车)
OK, successfully used password, moving on...
Set root password? [Y/n] y (设置数据库密码)
New password: (输入密码)
Re-enter new password: (再次确认)
Password updated successfully!
Reloading privilege tables..
... Success!
Remove anonymous users? [Y/n] y (将数据库放进生产环境)
... Success!
Disallow root login remotely? [Y/n] n (不仅仅允许root登录)
... skipping.
Remove test database and access to it? [Y/n] y (删除原有数据库)
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reload privilege tables now? [Y/n] y (立即生效所有更改)
... Success!
Thanks for using MariaDB!
8.修改mysql1节点的数据库配置文件/etc/my.cnf(同2)
[root@mysql1 ~]# cat /etc/my.cnf (修改这个文件 添加以下这三项)
[mysqld]
log_bin = mysql-bin (记录操作日志)
binlog_ignore_db = mysql1 (不同步mysql系统数据库)
server_id = 10 (数据库集群中每个节点id都要不同)
#一般使用IP地址的最后段的数字,例如192.168.200.10,server_id就写10
###############
9.重启数据库服务,并进入数据库(同2)
[root@mysql1 ~]# systemctl restart mariadb (重启数据库)
[root@mysql1 ~]# mysql -uroot -p000000 (登录)
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
10.在mysql1节点,授权在任何客户端机器上可以以root用户登录到数据库,然后在主节点上创建一个user用户连接节点mysql2,并赋予从节点同步主节点数据库的权限
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by "000000";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';
Query OK, 0 rows affected (0.00 sec)
11.在从节点mysql2上登录MariaDB数据库,配置从节点连接主节点的连接信息。
MariaDB [(none)]> change master to master_host='mysql1',master_user='user',master_password='000000';
Query OK, 0 rows affected (0.00 sec)
(这里的mysql1是我的主数据库,user是主数据库用户,000000我的数据库密码)
12.配置完毕主从数据库之间的连接信息之后,开启从节点服务
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 814
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
###############################
1 row in set (0.00 sec)
可以看到Slave_IO_Running和Slave_SQL_Running的状态都是Yes,配置数据库主从集群成功。