MySQL主从部署

MySQL主从部署

参考：https://www.cnblogs.com/larks-islands/p/18212752

获取安装包：

cd /data
 mkdir -p /data/mysql-install
 cd mysql-install
 wget https://cdn.mysql.com/archives/mysql-8.0/mysql-server_8.0.44-1ubuntu22.04_amd64.deb-bundle.tar
 tar -xf mysql-server_8.0.44-1ubuntu22.04_amd64.deb-bundle.tar

安装

dpkg -i *.deb

创建/data/mysql 数据目录

mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
chmod 750 /data/mysql
chmod 755 /data

修改mysql配置文件

cd /etc/mysql/conf.d
vim mysqld.conf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /data/mysql
log-error       = /var/log/mysql/error.log
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

配置apparmor，增加新数据目录的访问权限：

vim /etc/apparmor.d/usr.sbin.mysqld

增加

/data/mysql/ r,
/data/mysql/** rwk,

systemctl reload apparmor

重启mysql

systemctl restart mysql

修改root用户密码

mysql -uroot

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Fangz*'; # 已隐藏真实密码
mysql > create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Fangzh*';   # 已隐藏真实密码
mysql > grant all privileges on *.* to 'root'@'%' with grant option;
mysql > flush privileges;

在master机器

修改后的配置文件

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket                = /var/run/mysqld/mysqld.sock
datadir                = /data/mysql
log-error        = /var/log/mysql/error.log
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 主数据库的唯一标识符
server-id=1
# 启用二进制日志，用于记录主数据库的所有更新操作
log-bin=mysql-bin
# 指定复制的数据库名称，可指定多个数据库，每行一个
binlog-do-db=textile

创建复制用户

mysql > CREATE USER 'fangzhi'@'%' IDENTIFIED BY 'fangzhi*';   # 真实密码已隐藏
mysql > GRANT REPLICATION SLAVE ON *.* TO 'fangzhi'@'%'; 
mysql > FLUSH PRIVILEGES; 

创建纺织后台用户

mysql > CREATE USER 'textile'@'%' IDENTIFIED BY 'Textile_2026';   # 真实密码已隐藏
mysql > GRANT all ON textile.* TO 'textile'@'%'; 
mysql > GRANT select ON mysql.* TO 'textile'@'%'; 
mysql > FLUSH PRIVILEGES; 

在slave机器

修改后的配置文件 /etc/mysql/conf.d/mysqld.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket                = /var/run/mysqld/mysqld.sock
datadir                = /data/mysql
log-error        = /var/log/mysql/error.log
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 从数据库的唯一标识符
server-id=2
# 启用中继日志，用于在从数据库上复制主数据库的操作
relay-log=mysql-relay-bin
# 设置从数据库为只读，防止在从数据库上直接写入数据
read-only=1

登录从库，配置从数据库连接到主数据库

mysql -uroot -p                                  #登录mysql
CHANGE MASTER TO MASTER_HOST='192.16.10.135',    #指向主库             
MASTER_USER='fangzhi',                             #用于复制的MySQL账户
MASTER_PASSWORD='fangzhi*',                      #密码，此处真实密码已隐藏
MASTER_LOG_FILE='mysql-bin.000001',              #主库bin log名称，主库上用SHOW MASTER STATUS查看
MASTER_LOG_POS=1548;                              #主库bin log位置，主库上用SHOW MASTER STATUS查看
                      
START SLAVE;                                     #启动从库进程
SHOW SLAVE STATUS\G;                             #显示进程状态

等一会，正常的话如下：

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.100.135
                  Master_User: fangzhi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1548
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.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: 1548
              Relay_Log_Space: 536
              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: e01320ea-41ca-11f1-a19e-fefcfe3c2da2
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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, 1 warning (0.00 sec)