MySQL备份+主从服务器搭建

MySQL备份+主从服务器搭建

MySQL主服务器：192.168.77.144

MySQL从服务器：192.168.77.145

搭建MySQL服务

配置firewall防火墙

[root@slave-mysql ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@slave-mysql ~]# firewall-cmd --reload

安装MySQL

[root@master-mysql ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@master-mysql ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
[root@master-mysql ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@master-mysql ~]# yum -y install mysql-community-server

启动MySQL，并设置自启动

[root@master-mysql ~]# systemctl start mysqld
[root@master-mysql ~]# systemctl enable mysqld

查看MySQL密码

[root@master-mysql ~]# cat /var/log/mysqld.log | grep password

设置MySQL

[root@master-mysql ~]# mysql -u root -p
mysql> alter user root@localhost identified by 'Xiaoxiong123!'; # 第一次密码需要设置的复杂些
mysql> show variables like 'validate_password%'; # 根据需要设置对于密码强度
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    | # 检测用户名是否重复，不常用
| validate_password_dictionary_file    |        | # 字典文件，启动后，密码需要符合字典
| validate_password_length             | 8      | # 密码最小长度，最小为4
| validate_password_mixed_case_count   | 1      | # 至少n对大小写字母
| validate_password_number_count       | 1      | # 最少数字数量
| validate_password_policy             | MEDIUM | # 安全强度
| validate_password_special_char_count | 1      | # 最少字符数量
+--------------------------------------+--------+

配置MySQL配置文件

[root@master-mysql ~]# cp -a /etc/my.cnf /etc/my.cnf.back # 配置前一定要备份

主库配置：
[root@master-mysql ~]# systemctl restart mysqld
[root@slave-mysql ~]# mysql -u root -p
mysql> grant replication slave on *.* to slave@'192.168.77.145' identified by 'Slave123!';   # ip为从服务器地址
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      451 |              |                  |               
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从库配置：
[root@master-mysql ~]# systemctl restart mysqld
[root@slave-mysql ~]# mysql -u root -p
mysql> change master to
    -> master_user='slave',  				# 主服务器设置的用户
    -> master_password='Slave123!',
    -> master_host='192.168.77.144',		# 主服务器的ip
    -> master_log_file='mysql-bin.000002',	# 对应的bin文件名称
    -> master_log_pos=451;					# 当前bin文件大小
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
mysql> show slave status\G;

验证主从数据库是否正常：