@TOC
一、部署前准备
1.开放3306端口或直接关闭防火墙
因为MySQL主从之间需要通信，所以必须开放各自的端口，默认情况下是3306端口
①查看防火墙状态
firewall-cmd --state
②开放3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
③重新加载防火墙规则
firewall-cmd --reload
④查看已开放端口
firewall-cmd --list-ports
2.下载并上传MySQL安装包到服务器
下载地址：https://downloads.mysql.com/archives/community/
⑥创建MySQL安装目录并解压
我这边是在公司服务器上安装，考虑到数据库需要的存储空间比较大，所以挂载了一块磁盘，将整块盘都给MySQL用了，所以安装目录是根目录下的/mysql，各位读者可以随意在哪建目录都行
解压：tar -zxvf mysql-5.7.31-el7-x86_64.tar.gz -C /mysql
3.添加MySQL用户和组
userdel mysql
groupadd mysql
useradd -g mysql mysql
passwd mysql
mkdir -p /mysql/data # 数据目录
chown -R mysql:mysql /mysql
二、开始部署
切换到mysql用户下部署，当然用root用户也可以
su - mysql
cd /mysql
1.创建my.cnf文件
!!!!
主从的配置文件唯一区别就是server-id
我这里设置主为1，从为2
vim my.cnf
[client]
socket=/mysql/data/mysql.sock
[mysqld]
server-id=1
port = 3306
basedir=/mysql/mysql
datadir=/mysql/data
socket=/mysql/data/mysql.sock
log-error=/mysql/data/mysqld.log
pid-file=/mysql/data/mysqld.pid
log-bin=/mysql/data/binlog
slow_query_log_file = /mysql/data/slow.log
log_slave_updates
expire_logs_days=7
binlog_format=ROW
#default_authentication_plugin=mysql_native_password //修改密码加密方式
gtid-mode = on
enforce-gtid-consistency = 1
skip_slave_start=1
innodb_buffer_pool_size = 1000M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 24M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200
innodb_io_capacity_max = 600
innodb_thread_concurrency = 0
innodb_autoinc_lock_mode = 2
innodb_lock_wait_timeout = 60
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_max_dirty_pages_pct = 80
innodb_autoextend_increment = 512
innodb_checksum_algorithm = NONE
max_connect_errors = 1000
max_connections = 500
connect_timeout = 300
wait_timeout = 86400
interactive_timeout = 86400
2.配置环境
# 用root用户
rm -rf /etc/my.cnf
ln -s /mysql/my.cnf /etc/my.cnf
ln -s /mysql/mysql-5.7.31-el7-x86_64 /mysql/mysql
cp /mysql/mysql/support-files/mysql.server /etc/init.d/mysql
# 令service可以启动mysql
chkconfig --add mysql
# mysql开机启动
chkconfig mysql on
# 切换到mysql用户
# 配置环境变量
vim ~/.bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
MYSQL_HOME=/mysql/mysql
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin
export PATH
:wq
#保存后刷新配置
source ~/.bash_profile
3.初始化MySQL
mysqld --defaults-file=/mysql/my.cnf --user=mysql --basedir=/mysql/mysql/ --datadir=/mysql/data/ --initialize
4.启动MySQL
mysqld_safe --defaults-file=/mysql/my.cnf --user=mysql &
登录MySQL
[mysql@192 mysql]$ grep "password" /mysql/data/mysqld.log
2021-03-04T13:22:10.881586Z 1 [Note] A temporary password is generated for root@localhost: z8h-MF,lx!0L
[mysql@192 mysql]$ mysql -u root -p
Enter password: # 输入上面root@localhost: 后的临时密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 创建新的密码
mysql > set password for root@'localhost' = '你的密码';
# 创建远程用户
mysql > create user 'root'@'%' identified by '你的密码';
#设置授权
mysql > grant all privileges on *.* to 'root'@'%' with grant option;
5.配置主库
# 创建用于主从复制的用户 这里'从服务器ip地址'也可以是'%'代表所有ip
mysql> CREATE USER 'repl'@'从服务器ip地址' IDENTIFIED WITH mysql_native_password BY '密码';
# 赋权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从服务器ip地址';
# 刷新权限
mysql> flush PRIVILEGES;
# 查看master状态
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 1471 | | | a35148ef-7cec-11eb-8e8e-000c29c5e6ab:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
记下binlog.000002和1471
6.配置从库
# 连接主库
mysql> CHANGE MASTER TO
-> MASTER_HOST='主服务器ip地址',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='密码',
-> MASTER_LOG_FILE='binlog.000002',
-> MASTER_LOG_POS=1471;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
# 开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 查看从库状态，两个yes代表部署成功
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.19.36
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1471
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 1471
Relay_Log_Space: 522
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: 1
Master_UUID: a35148ef-7cec-11eb-8e8e-000c29c5e6ab
Master_Info_File: /mysql/data/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: 9fd95dd6-7cec-11eb-8f02-000c29927484:1-3
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
7.测试主从
# 主库执行
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
# 从库执行
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
成功！
三、管理MySQL
查看状态
service mysql status
关闭MySQL
service mysql stop
开启MySQL
第一次启动需要用mysqld_safe --defaults-file=/mysql/my.cnf --user=mysql &
后面都可以用service mysql start
四、其他故障
用mysql -uroot -p登录输入密码之后有可能会提示
Can't connect to local MySQL server through socket '/tmp/mysql.sock'
这时可以用root用户建个软链接
ln -s /mysql/data/mysql.sock /tmp/mysql.sock
其他问题欢迎留言
Q.E.D.
