Ⅰ、环境说明
1、两台服务器(centos8)
2、MySQL版本号
8.0.21
Ⅱ、安装两台MySQL
注意主从MySQL版本要一致
1、获取安装包并安装
wget https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum install mysql-server -y
2、初始化MySQL
# 初始化MySQL
mysqld --initialize
3、启停MySQL并设置开机自启
- mysql启 / 停 / 重启
service mysqld start / stop / restart
- 查看MySQL运行状态
service mysqld status
- 设置MySQL开启自启
systemctl enable mysqld
systemctl daemon-reload
4、更改root用户密码
①、先查看MySQL的root用户的默认密码
sudo grep 'temporary password' /var/log/mysql/mysqld.log
②、使用刚才的密码登录MySQL
mysql -u root -p
③、更改root用户密码:
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'wenpan';
mysql> flush privileges;
mysql> quit;
再次使用 mysql -uroot -p
登陆 mysql,密码就是刚刚修改的密码了。
④、设置允许远程连接
mysql安装完毕后root账户默认不能远程连接!
mysql -u root -p
mysql>use mysql;
mysql>select host,user from user;
mysql>update user set host = '%' where user ='root';
mysql>flush privileges;
mysql>select host,user from user;
5、创建一个用户并授权
并不是所有客户端都通过root账户连接MySQL,这里我们可以创建一个用户,并给他授予相应的权限(后面会用到)!
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
# 这里授予所有权限,可自己配置
GRANT ALL ON *.* TO '用户名'@'%';
可参考:https://www.cnblogs.com/remixnameless/p/13462937.html
Ⅲ、搭建主从
①、主从复制原理
②、修改主服务器master配置
可在该配置文件中配置主从复制具体需要复制哪些库!
# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin # [必须]启用二进制日志
server-id=26 # [必须]服务器唯一ID,默认是1,一般取IP最后一段
③、修改从服务器slave配置
可在该配置文件中配置主从复制具体需要复制哪些库!
# vi /etc/my.cnf
[mysqld]
# 启用binlog并指定存储目录和文件名,可自己指定二进制日志目录
log-bin=mysql-bin # [不是必须]启用二进制日志
server-id=98 # [必须]服务器唯一ID,默认是1,一般取IP最后一段
④、重启两台服务器的mysql
systemctl restart mysqld
⑤、查看主服务器master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 156 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
⑥、配置从服务器
指定master的主机,用户名和密码,日志文件等!
# 从服务器上输入如下命令:
mysql> change master to master_host='xx.xx.xx.xx',master_user='wenpan',master_password='wenpan',
master_log_file='mysql-bin.000001',master_log_pos=156;
# 启动从服务器复制功能
mysql> start slave;
⑦、查看从服务器状态
注意:
Slave_IO_Running
及Slave_SQL_Running
进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。以上操作过程,主从服务器配置完成。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.xx # 主服务器ip地址
Master_User: wenpan # 授权帐户名,尽量避免使用root
Master_Port: 3306 # 数据库端口,部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 156 # 同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
Relay_Log_File: iZuf60p2g1civuyg9sj9o4Z-relay-bin.000005
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes # 此状态必须YES
Slave_SQL_Running: Yes # 此状态必须YES
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 551
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 245
Master_UUID: 5ed68047-356f-11eb-a490-00163e06f9c9
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
⑧、测试
1、在master上顺序执行下面语句
mysql> create database project_test;
mysql> use project_test;
mysql> create table project_test(id int(4),name char(10));
mysql> insert into project_test values(001,'wenpan');
2、从服务器上验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| project_test |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use project_test
mysql> select * from project_test;
+------+--------+
| id | name |
+------+--------+
| 1 | wenpan |
+------+--------+
1 row in set (0.00 sec)
完毕!!!