安装环境
操作系统 | ubuntu16.04 |
Mysql | 5.7.33 |
CPU | 8 |
MEM | 16G |
SYS Disk | 60G |
DATA Disk | 500G |
网络规划
ROLE | HOSTNAME | IP |
master | mysql-01 | 172.16.199.78 |
slave | mysql-02 | 172.16.199.109 |
slave | mysql-03 | 172.16.199..131 |
系统初始化(所有节点)
1.时区设置
#设置时区为上海
timedatectl set-timezone Asia/Shanghai
#查看是否修改成功
timedatectl status
2.时间同步
#修改NTP服务器为阿里云
sed -ri '/^#NTP/s/(.*)(=.*)/NTP=ntp.aliyun.com/' /etc/systemd/timesyncd.conf
sed -ri '/^#FallbackNTP/s/(.*)(=.*)/FallbackNTP=ntp1.aliyun.com/' /etc/systemd/timesyncd.conf
#重启时间同步服务
systemctl restart systemd-timesyncd.service
3.数据盘处理
#格式化
mkfs.ext4 /dev/sdb
#编辑fstab,增加内容
vim /etc/fstab
/dev/sdb /var/lib/mysql ext4 defaults 0 2
#临时挂载到/mnt
mount /dev/sdb /mnt
安装部署
4.安装Mysql Master节点
#安装mysql-server,安装完成后会提示为root设置密码,根据提示完成。
apt-get install mysql-server -y
#mysql安全设置,输入刚才设置好的密码。
mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
#编辑mysqld.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 172.16.199.78
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 3000
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
server-id = 1
log_bin=master-binlog
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = row
gtid_mode = on
enforce_gtid_consistency = on
character-set-server = utf8
collation-server = utf8_general_ci
#停止mysql服务
systemctl stop mysql.service
#移动数据目录文件
mv /var/lib/mysql/* /mnt/
#卸载/mnt
umount /mnt
#挂在数据盘到/var/lib/mysql
mount -a
#修改权限
chown mysql:mysql /var/lib/mysql
chmod 770 /var/lib/mysql
#启动mysql服务
systemctl start mysql.service
5.Master节点创建复制账号
mysql -uroot -p
#查看gtid模式是否启动成功,有Executed_Gtid_Set表示成功
mysql> show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| master-binlog.000001 | 168 | | | c60a8665-7738-11ec-bd3c-fa163e12cffd:1-7 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#设置复制账号
mysql> grant replication slave, replication client on *.* to 'repl'@'%' identified by 'repl';
6.安装Mysql Slave节点
#安装mysql-server,安装完成后会提示为root设置密码,根据提示完成。
apt-get install mysql-server -y
#mysql安全设置,输入刚才设置好的密码,过程与master节点设置一样。
mysql_secure_installation
#编辑mysql-02的mysqld.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 172.16.199.109
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 3000
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
server-id = 2
log_bin = slave-binlog
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = row
gtid_mode = on
enforce_gtid_consistency = on
character-set-server = utf8
collation-server = utf8_general_ci
#编辑mysql-03的mysqld.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 172.16.199.131
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 3000
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
server-id = 3
log_bin = slave-binlog
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = row
gtid_mode = on
enforce_gtid_consistency = on
character-set-server = utf8
collation-server = utf8_general_ci
#停止mysql服务
systemctl stop mysql.service
#移动数据目录文件
mv /var/lib/mysql/* /mnt/
#卸载/mnt
umount /mnt
#挂在数据盘到/var/lib/mysql
mount -a
#修改权限
chown mysql:mysql /var/lib/mysql
chmod 770 /var/lib/mysql
#启动mysql服务
systemctl start mysql.service
7.Slave节点启动复制
mysql -uroot -p
#设置master连接信息
mysql> CHANGE MASTER TO MASTER_HOST='172.16.199.78', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1;
#启动复制
mysql> start slave;
#查看复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.199.78
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000001
Read_Master_Log_Pos: 168
Relay_Log_File: mysql-02-relay-bin.000005
Relay_Log_Pos: 1039
Relay_Master_Log_File: master-binlog.000001
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: 168
Relay_Log_Space: 1340
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: c60a8665-7738-11ec-bd3c-fa163e12cffd
Master_Info_File: /var/lib/mysql/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: c60a8665-7738-11ec-bd3c-fa163e12cffd:1-7
Executed_Gtid_Set: 7f6ecc56-776d-11ec-bf69-fa163e4b3586:1-4,
c60a8665-7738-11ec-bd3c-fa163e12cffd:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)