系统环境

Ubuntu 20.04.3 LTS

MySQL 版本

MySQL 5.7.43

机器环境规划

MySQL-Master-5.7    10.2.0.47
MySQL-Slave-5.7     10.2.0.48

安装依赖

apt install -y libaio-dev libncurses5

下载二进制软件包并解压至/usr/local/

官方下载地址：https://downloads.mysql.com/archives/community/

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz
tar zxvf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ && ln -sf /usr/local/mysql-5.7.43-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.43

创建 mysql 用户和数据目录

useradd mysql -s /usr/sbin/nologin && mkdir -p /apps/mysql/ && chown -R mysql:mysql /apps/mysql

添加环境变量

echo "export PATH=/usr/local/mysql-5.7.43/bin:$PATH" > /etc/profile.d/mysql.sh && source /etc/profile.d/mysql.sh

初始化MySQL数据库

mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.43 --datadir=/apps/mysql/ --user=mysql

--initialize-insecure：以不安全的方式初始化数据目录，为root用户设置一个空密码。
--basedir=/usr/local/mysql-5.7.43 ：MySQL的二进制文件、库文件等组件所在的根目录。MySQL的安装目录。
--datadir=/apps/mysql/ ：存储数据库文件（如表定义、数据文件等）的地方。
--user=mysql ： 指定用户运行MySQL服务

创建日志目录

mkdir /apps/mysql/binlog && touch /apps/mysql/binlog/logbin.index && touch /apps/mysql/mysql-error.log && chown -R mysql:mysql /apps/mysql

创建主节点配置文件

cat >> /etc/my.cnf <<EOF
[mysqld]
user = mysql
basedir = /usr/local/mysql-5.7.43
datadir = /apps/mysql/
server_id = 1
port = 3306
socket = /tmp/mysql.sock
log_bin = /apps/mysql/binlog/logbin
binlog_format = ROW
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 10
interactive_timeout = 10
innodb_data_home_dir = /apps/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /apps/mysql/
innodb_buffer_pool_size = 64M
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
general_log_file=/apps/mysql/general.log
general_log=1
log_bin_trust_function_creators = 1
log-error = /apps/mysql/mysql-error.log
slow-query-log = 1
slow-query-log-file = /apps/mysql/mysql-slow.log
long_query_time = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_preserve_commit_order = 1

[mysql]
socket = /tmp/mysql.sock
no-auto-rehash
default-character-set = utf8
EOF

查看mysqld 启动时加载配置文件的顺序

mysqld --help --verbose | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

创建从节点配置文件

cat >> /etc/my.cnf <<EOF
[mysqld]
user = mysql
basedir = /usr/local/mysql-5.7.43
datadir = /apps/mysql/
server_id = 2
port = 3306
socket = /tmp/mysql.sock
binlog_format = mixed
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
wait_timeout = 10
interactive_timeout = 10
innodb_data_home_dir = /apps/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /apps/mysql/
innodb_buffer_pool_size = 64M
innodb_log_file_size = 2G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
general_log_file=/apps/mysql/general.log
general_log=1
log_bin_trust_function_creators = 1
log-error = /apps/mysql/mysql-error.log
slow-query-log = 1
slow-query-log-file = /apps/mysql/mysql-slow.log
long_query_time = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_preserve_commit_order = 1

[mysql]
socket=/tmp/mysql.sock
no-auto-rehash
default-character-set = utf8
EOF

拷贝启动脚本

cp /usr/local/mysql-5.7.43/support-files/mysql.server /etc/init.d/mysqld

首次启动

/etc/init.d/mysqld start

后续管理

systemctl stop mysql
systemctl start mysql
systemctl restart mysql
systemctl status mysql

设置MySQL root 密码

mysqladmin -uroot password

注意：以上操作是搭建mysql服务，主从节点都要做

检查主库是否开启 binlog

mysql -uroot -pPASSWD -e "select @@log_bin;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

主库创建复制用户

mysql -uroot -pPASSWD -e "GRANT ALL PRIVILEGES ON . TO 'user'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;"

查询是否创建成功

mysql -uroot -pPASSWD -e "select user,host from mysql.user;"

查询master_log_pos和master_log_file值(主服务器操作)

mysql -uroot -pPASSWD -e "SHOW MASTER STATUS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| logbin.000002 |      713 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

master_connect_retry=10; 如果从库尝试连接主库失败，它将每隔10秒重新尝试一次连接。

告知从库复制信息(从服务器操作)

mysql -uroot -pPASSWD -e "CHANGE MASTER TO master_host='主节点IP', master_user='复制用户的用户名', master_password='复制用户的密码', master_port=3306, master_log_file='logbin.000002', master_log_pos=713, master_connect_retry=10;"

从库开启复制线程(从服务器操作)

mysql -uroot -pPASSWD -e "start slave;"

如果有问题

STOP SLAVE;  #先关闭复制线程
RESET SLAVE; #然后重置从服务器（slave）的复制配置和状态的操作

验证主从状态：（从服务器操作）

mysql -uroot -pPASSWD -e "SHOW SLAVE STATUS\G;"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.0.47
                  Master_User: kevin
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: logbin.000002
          Read_Master_Log_Pos: 713
               Relay_Log_File: MySQL-Slave-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: logbin.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: 713
              Relay_Log_Space: 530
              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: 3
                  Master_UUID: ade9cb95-1329-11ef-8929-005056b3adf8
             Master_Info_File: /apps/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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

验证是否能同步数据：

1. 在主服务器操作：

mysql -uroot -pPASSWD -e "create database qwx;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -pPASSWD -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| binlog             |
| mysql              |
| performance_schema |
| qwx                |
| sys                |
+--------------------+

2. 从服务器验证：

mysql -uroot -pPASSWD -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| binlog             |
| mysql              |
| performance_schema |
| qwx                |
| sys                |
+--------------------+
最后修改：2024 年 05 月 18 日
© 禁止转载
如果觉得我的文章对你有用，请随意赞赏