Centos下mysql主从配置
2019-06-30 本文已影响0人
小小小笑呵
原理
见参考 1 和 参考2
环境配置
| 主机 | 描述 |
|---|---|
| 192.168.231.149 | master |
| 192.168.231.151 | slave-2 |
配置流程
master节点
- 配置master节点的配置文件
- 创建专门用来进行主从复制的账号
- 锁定数据库，并且将当前master中的数据导出，并且记录当前的binlog的文件名称和位置
slave节点
- 配置slave节点的配置文件
- 导入master数据库中的文件
- 使用change master to命令使其成为slave节点
注意：先尝试在slave节点上连接master节点的mysql-server，提前查看是否有防火墙等问题
mysql的安装
1. 下载相应的软件源安装包，并且生成yum
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
[root@localhost ~]# yum localinstall mysql57-community-release-el7-8.noarch.rpm # 生成mysql的yum源
[root@localhost ~]# ls /etc/yum.repos.d/ # 查看是否有mysql的yum源
CentOS-Base.repo CentOS-CR.repo CentOS-Debuginfo.repo CentOS-fasttrack.repo CentOS-Media.repo CentOS-Sources.repo CentOS-Vault.repo mysql-community.repo mysql-community-source.repo
2. 下载mysql-server
[root@localhost ~]# yum search mysql # 查找mysql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.lzu.edu.cn
* extras: mirrors.cqu.edu.cn
* updates: mirrors.cqu.edu.cn
============================================================================================================== N/S matched: mysql ===============================================================================================================
MySQL-python.x86_64 : An interface to MySQL
akonadi-mysql.x86_64 : Akonadi MySQL backend support
apr-util-mysql.x86_64 : APR utility library MySQL DBD driver
dovecot-mysql.x86_64 : MySQL back end for dovecot
freeradius-mysql.x86_64 : MySQL support for freeradius
libdbi-dbd-mysql.x86_64 : MySQL plugin for libdbi
mysql-community-client.i686 : MySQL database client applications and tools
....
mysql-community-server.x86_64
...
[root@localhost ~]# yum install mysql-community-server.x86_64
...
Installed:
mysql-community-libs.x86_64 0:5.7.26-1.el7 mysql-community-libs-compat.x86_64 0:5.7.26-1.el7 mysql-community-server.x86_64 0:5.7.26-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:5.7.26-1.el7 mysql-community-common.x86_64 0:5.7.26-1.el7 net-tools.x86_64 0:2.0-0.24.20131004git.el7 perl.x86_64 4:5.16.3-294.el7_6 perl-Carp.noarch 0:1.26-244.el7
perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7
perl-Getopt-Long.noarch 0:2.40-3.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-PathTools.x86_64 0:3.40-5.el7 perl-Pod-Escapes.noarch 1:1.04-294.el7_6 perl-Pod-Perldoc.noarch 0:3.20-4.el7
perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-4.el7 perl-Storable.x86_64 0:2.45-3.el7
perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-294.el7_6
perl-macros.x86_64 4:5.16.3-294.el7_6 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7
Replaced:
mariadb-libs.x86_64 1:5.5.60-1.el7_5
Complete!
配置master节点
[root@localhost ~]# systemctl start mysqld # 开启mysql服务
[root@localhost ~]# systemctl enable mysqld # 开机就启动mysql服务
[root@localhost ~]# cat /etc/my.cnf # 查看mysql的的配置文件，其中mysql的日志文件中存放着root用户的初始密码
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]# cat /var/log/mysqld.log | grep password # 查看日志文件为root用户初始化的密码
2019-06-30T13:08:24.277971Z 1 [Note] A temporary password is generated for root@localhost: my<:IhMf*8hb
[root@localhost ~]# mysql -u root -p # 根据上面的密码登陆mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ABCabc123...'; # 修改root的密码
# 模拟一点mysql的数据出来
mysql> create database user;
Query OK, 1 row affected (0.00 sec)
mysql> use user;
Database changed
mysql> create table user(name char(20), age int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user(name, age) values ('allen', 15);
Query OK, 1 row affected (0.08 sec)
mysql> insert into user(name, age) values ('ketty', 18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-------+------+
| name | age |
+-------+------+
| allen | 15 |
| ketty | 18 |
+-------+------+
2 rows in set (0.00 sec)
# 创建一个专门用来进行主从复制的用户，并且授权
mysql> CREATE USER 'slave2'@'192.168.231.151' IDENTIFIED BY 'ABCabc123...';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.231.151';
mysql> FLUSH PRIVILEGES;
mysql> select user, host from mysql.user;
+---------------+-----------------+
| user | host |
+---------------+-----------------+
| slave2 | 192.168.231.151 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------------+
5 rows in set (0.00 sec)
# 查看一下未配置的master的状态
mysql> show master status;
Empty set (0.00 sec)
# 配置mysql的配置文件，修改如下两项
[root@localhost ~]# vi/etc/my.cnf
log_bin=mysql-bin
server_id=1
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;
# 打开另外一个终端，导出数据库中数据
[root@localhost ~]# mysqldump -u root -p --all-databases --master-data > dbdump.db
slave节点的配置
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
# 测试远程连接一下master
[root@localhost ~]# mysql -h 192.168.231.149 -P 3306 -u slave2 -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.231.149' (113)
# 在master节点上输入iptables -F即可以解决
# 修改配置文件并且修改root账号的密码
[root@localhost ~]# vi /etc/my.cnf
server_id=3
read_only=1
super_read-only=1
# 一般slave节点需要设置为只读权限，由需要根据不同的用户设置不同的只读权限，详情细节见参考3
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# cat /var/log/mysqld.log | grep password
2019-07-01T02:13:57.213304Z 1 [Note] A temporary password is generated for root@localhost: d#Ar&E9-/>z.
[root@localhost ~]# mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ABCabc123...';
# 导入来自于master节点的数据
[root@localhost ~]# mysql -u root -p < dbdump.db
# 将该节点变成slave节点
[root@localhost ~]# mysql -u root -p
mysql> show slave status;
Empty set (0.00 sec)
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.231.151',
MASTER_USER='slave2',
MASTER_PASSWORD='ABCabc123...',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
...
Slave_IO_Running: Yes # 这两个字段为yes表示成功
Slave_SQL_Running: Yes
...
测试
1 在master节点
mysql> unlock tables; # 解除对表的锁定
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table company(name char(20), money int);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into company(name, money) values ('google', 20000);
Query OK, 1 row affected (0.20 sec)
mysql> insert into company(name, money) values ('facebook', 20000);
Query OK, 1 row affected (0.03 sec)
2 在slave节点上查看数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| user |
+--------------------+
6 rows in set (0.01 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from company;
+----------+-------+
| name | money |
+----------+-------+
| google | 20000 |
| facebook | 20000 |
+----------+-------+
2 rows in set (0.00 sec)
(喜欢的朋友，记得点个赞，有疑问请在下方评论)
参考
1 https://www.jianshu.com/p/b0cf461451fb
