<aside> 📌 MySQL（8.4.0）的集群化部署具备高可用性，可以更好的保护业务系统的正常运作，所以这种模式是生产环境必备的安装模式。 官网下载地址：https://dev.mysql.com/downloads/mysql/
</aside>
~ ❯ lxc start mysql-node{1,2,3}
~ ❯ lxc list
+-----------------+---------+-----------------------+-------------------------------------------------+-----------------+-----------+
| NAME | STATE | IPV4 | IPV6 | TYPE | SNAPSHOTS |
+-----------------+---------+-----------------------+-------------------------------------------------+-----------------+-----------+
| mysql-node1 | RUNNING | 10.10.10.187 (enp5s0) | fd42:cab6:b0d5:52af:216:3eff:fe61:75e2 (enp5s0) | VIRTUAL-MACHINE | 0 |
+-----------------+---------+-----------------------+-------------------------------------------------+-----------------+-----------+
| mysql-node2 | RUNNING | 10.10.10.252 (enp5s0) | fd42:cab6:b0d5:52af:216:3eff:fe5f:a9af (enp5s0) | VIRTUAL-MACHINE | 0 |
+-----------------+---------+-----------------------+-------------------------------------------------+-----------------+-----------+
| mysql-node3 | RUNNING | 10.10.10.23 (enp5s0) | fd42:cab6:b0d5:52af:216:3eff:fe84:b6ca (enp5s0) | VIRTUAL-MACHINE | 0 |
+-----------------+---------+-----------------------+-------------------------------------------------+-----------------+-----------+
准备三台 kvm 虚拟机，其中 mysql-node1 为主库（master），mysql-node{2,3} 为从库（slave）
sudo pacman -Sy gcc-libs lib32-gcc-libs lib32-icu icu
sudo pacman -S mysql
mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql #初始化生成默认 root 用户和密码
#---------------------------------输出日志------------------------------------
2024-07-02T14:08:47.148107Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2024-07-02T14:08:47.148752Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-07-02T14:08:47.148782Z 0 [System] [MY-013169] [Server] /usr/bin/mysqld (mysqld 8.4.0) initializing of server in progress as process 959
2024-07-02T14:08:47.176509Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-02T14:08:47.996751Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-02T14:08:52.643800Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: **k;a#7F)cnLCq**
2024-07-02T14:08:58.859425Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
#----------------------------------------------------------------------------
# echo "skip-grant-tables" >> /etc/mysql/my.cnf #不开启验证登录
echo "server_id=1" >> /etc/mysql/my.cnf #主节点服务id
echo "log_bin=master1" >> /etc/mysql/my.cnf #主节点日志
echo "binlog-do-db=school" >> /etc/mysql/my.cnf #要同步的数据库
echo "binlog-ignore-db=mysql,information_schema,performance_schema" >> /etc/mysql/my.cnf #不同步的库
echo "log-error=/var/lib/mysql/mysql_error.log" >> /etc/mysql/my.cnf #错误日志配置
echo "general_log=on" >> /etc/mysql/my.cnf #开启普通日志
echo "general_log_file=/var/lib/mysql/mysql_masterl_log.log" >> /etc/mysql/my.cnf ## 普通日志配置
echo "mysql_native_password=ON" >> /etc/mysql/my.cnf #加载 mysql_native_password 密码插件
systemctl restart mysqld
systemctl status mysqld
systemctl enable mysqld #确保状态正常就可以 enable 服务
[root@archlinux ~]# mysql -uroot -p
Enter password: **k;a#7F)cnLCq #初始化root@localhost密码**
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 8
Server version: 8.4.0 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> alter user 'root'@'localhost' identified by '123'; #修改本地root密码123
mysql> flush privileges; #刷新权限
mysql> use mysql; #选择表
mysql> create user 'slave'@'%' identified by '123'; #创建远程slave用户密码123
mysql> # alter user 'slave'@'%' identified with mysql_native_password by '123'; # mysql_native_password 不插件不像 caching_sha2_password 那样强制使用安全连接
mysql> select host,user,plugin from user; #查看用户
mysql> grant all privileges on *.* to 'slave'@'%' with grant option; #授权远程root用户所有数据库和表
mysql> grant select, replication slave, replication client on *.* TO 'slave'@'%'; #授权主从复制
mysql> flush privileges; #刷新权限
mysql> show binary logs; #复制 log_name&file_size -> 从库 source_log_file&source_log_pos
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| master1.000001 | 1629 | No |
| master1.000002 | 1010 | No |
| master1.000003 | 945 | No |
| master1.000004 | 590 | No |
| master1.000005 | 181 | No |
| master1.000006 | 1783 | No |
+----------------+-----------+-----------+
sudo pacman -Sy gcc-libs lib32-gcc-libs lib32-icu icu
sudo pacman -S mysql
mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql #初始化
# echo "skip-grant-tables" >> /etc/mysql/my.cnf #不开启验证登录
echo "server_id=2" >> /etc/mysql/my.cnf #从节点服务id
echo "relay_log=slave1" >> /etc/mysql/my.cnf #从节点复制
echo "log_bin=node1" >> /etc/mysql/my.cnf #从节点日志
echo "binlog-do-db=school" >> /etc/mysql/my.cnf #要同步的数据库
echo "binlog-ignore-db=mysql,information_schema,performance_schema" >> /etc/mysql/my.cnf #不同步
echo "log-error=/var/lib/mysql/mysql_error.log" >> /etc/mysql/my.cnf #错误日志配置
echo "general_log=on" >> /etc/mysql/my.cnf #开启普通日志
echo "general_log_file=/var/lib/mysql/mysql_nodel_log.log" >> /etc/mysql/my.cnf ## 普通日志配置
echo "mysql_native_password=ON" >> /etc/mysql/my.cnf
systemctl restart mysqld
systemctl status mysqld
systemctl enable mysqld #确保状态正常就可以 enable 服务
[root@archlinux ~]# mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 8
Server version: 8.4.0 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> alter user 'root'@'localhost' identified by '123'; #修改本地root密码123
mysql> flush privileges; #刷新权限
mysql> use mysql; #选择表
mysql> select host,user,plugin from user; #查看用户
mysql> create user 'slave'@'%' identified by '123'; #创建远程slave用户密码123
mysql> # alter user 'slave'@'%' identified with mysql_native_password by '123';
mysql> select host,user,plugin from user; #查看用户
mysql> grant all privileges on *.* to 'slave'@'%' with grant option; #授权远程root用户所有数据库和表
mysql> grant select, replication slave, replication client on *.* TO 'slave'@'%'; #授权主从复制
mysql> flush privileges; #刷新权限
mysql> stop replica; #停止同步
mysql> change replication source to
-> source_host='10.10.10.187',
-> source_user='slave',
-> source_password='123',
-> source_port=3306,
-> source_log_file='master1.000002',
-> source_log_pos=1010;
mysql> start replica; #开始同步master
mysql> show replica status; #查看同步状态
mysql> show databases; #查看 school 数据库是否同步过来了
mysql> use school;
mysql> show tables;
mysql> select * from `school-info`; #查询表信息
sudo pacman -Sy gcc-libs lib32-gcc-libs lib32-icu icu
sudo pacman -S mysql
mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql
# echo "skip-grant-tables" >> /etc/mysql/my.cnf #不开启验证登录
echo "server_id=3" >> /etc/mysql/my.cnf #从节点服务id
echo "relay_log=slave2" >> /etc/mysql/my.cnf #从节点复制
echo "log_bin=node2" >> /etc/mysql/my.cnf #从节点日志
echo "binlog-do-db=school" >> /etc/mysql/my.cnf #要同步的数据库
echo "binlog-ignore-db=mysql,information_schema,performance_schema" >> /etc/mysql/my.cnf #不同步
echo "log-error=/var/lib/mysql/mysql_error.log" >> /etc/mysql/my.cnf #错误日志配置
echo "general_log=on" >> /etc/mysql/my.cnf #开启普通日志
echo "general_log_file=/var/lib/mysql/mysql_node2_log.log" >> /etc/mysql/my.cnf ## 普通日志配置
echo "mysql_native_password=ON" >> /etc/mysql/my.cnf
systemctl restart mysqld
systemctl status mysqld
systemctl enable mysqld #确保状态正常就可以 enable 服务
[root@archlinux ~]# mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 8
Server version: 8.4.0 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> alter user 'root'@'localhost' identified by '123'; #修改本地root密码123
mysql> flush privileges; #刷新权限
mysql> use mysql; #选择表
mysql> select host,user,plugin from user; #查看用户
mysql> create user 'slave'@'%' identified by '123'; #创建远程slave用户密码123
mysql> # alter user 'slave'@'%' identified with mysql_native_password by '123';
mysql> select host,user,plugin from user; #查看用户
mysql> grant all privileges on *.* to 'slave'@'%' with grant option; #授权远程root用户所有数据库和表
mysql> grant select, replication slave, replication client on *.* TO 'slave'@'%'; #授权主从复制
mysql> flush privileges; #刷新权限
mysql> stop replica; #停止同步
mysql> change replication source to
-> source_host='10.10.10.187',
-> source_user='slave',
-> source_password='123',
-> source_port=3306,
-> source_log_file='master1.000006', #来源Source_Log_File
-> source_log_pos=1783; #来源 Read_Source_Log_Pos
mysql> start replica; #开始同步master
mysql> show replica status; #查看同步状态（获取 source_log_file,source_log_pos）
mysql> show databases; #查看 school 数据库是否同步过来了
mysql> use school;
mysql> show tables;
mysql> select * from `school-info`; #查询表信息
建立主从关系之后，从库状态同步主库日志节点，那么在主库上创建数据库，信息就会自动同步到从库