MySQL 主从模式

装完 主数据库以后 修改配置文件 /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
server-id=1
log-bin = /var/lib/mysql/mysql-bin.log
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
# 允许最大连接数
max_connections=3000
# #服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#
# # 必须的配置（支持groupby）
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# # # 取消mysql表名大小写限制（0：区分，1：不区分）
lower_case_table_names=1
# # 临时表大小 默认16M 设置成 8192M
max_heap_table_size=8192M
# # MySQL服务器接收和发送的最大数据包大小。默认值为4M。设置为32M
max_allowed_packet=32M
# # 这个参数用于控制是否启用慢查询日志功能。默认情况下，它是禁用的。要启用慢查询日志，你需要将该参数设置为1
slow_query_log=1
# # 这个参数用于设置慢查询的阈值时间（以秒为单位）。默认值是10秒。你将其设置为4
long_query_time=4
# # 这个参数用于记录未使用索引的查询语句。如果设置为1，MySQL将在慢查询日志中记录那些未使用索引的查询语句
log-queries-not-using-indexes=1
# # 这个参数用于设置内存中临时表的最大大小。默认值为16MB。你将其设置为256M
tmp_table_size=256M
# # 设置时区（Idea连接mysql需要设置）
 default-time-zone='+08:00'
#
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置完以后 重启 mysql

systemctl start mysqld

启动 主数据库

mysql -u root -p

创建 从库 复制数据用的 账号

CREATE USER 'replication_copy'@'%' IDENTIFIED BY '密码';

赋予权限

GRANT REPLICATION SLAVE ON *.* TO 'replication_copy'@'%';

刷新权限

FLUSH PRIVILEGES;

查看主库信息  show master status;

要记住file 和 position的值，也就是第一和第二列，后面用的上

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

接着就是 从库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
server-id = 2
# 允许最大连接数
max_connections=3000
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8

# 必须的配置（支持groupby）
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# # 取消mysql表名大小写限制（0：区分，1：不区分）
lower_case_table_names=1
# 临时表大小 默认16M 设置成 8192M
max_heap_table_size=8192M
# MySQL服务器接收和发送的最大数据包大小。默认值为4M。设置为32M
max_allowed_packet=32M
# 这个参数用于控制是否启用慢查询日志功能。默认情况下，它是禁用的。要启用慢查询日志，你需要将该参数设置为1
slow_query_log=1
# 这个参数用于设置慢查询的阈值时间（以秒为单位）。默认值是10秒。你将其设置为4
long_query_time=4
# 这个参数用于记录未使用索引的查询语句。如果设置为1，MySQL将在慢查询日志中记录那些未使用索引的查询语句
log-queries-not-using-indexes=1
# 这个参数用于设置内存中临时表的最大大小。默认值为16MB。你将其设置为256M
tmp_table_size=256M
# 设置时区（Idea连接mysql需要设置）
default-time-zone='+08:00'
# 表示将 MySQL 设置为只读模式
read_only=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

从库可以 设置成 只读模式 但是这里 的只读 只针对于 普通用户账号

登录 从库

 mysql -u root -p

 停止 从库 存储服务

stop slave

配置 关联主库的信息

CHANGE MASTER TO MASTER_HOST='主库ip', MASTER_USER='replication_copy', MASTER_PASSWORD='主库密码', MASTER_LOG_FILE='主库二进制文件名', MASTER_LOG_POS= 主库二进制文件地址;

启动 服务

start slave

查看 是否 主从成功

show slave status \G;

 

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 主库IP地址
                  Master_User: replication_copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1513
               Relay_Log_File: 0012-relay-bin.000002
                Relay_Log_Pos: 1682
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes 这里是yes表示 已找到 主库二进制文件
            Slave_SQL_Running: Yes 这里是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: 1513
              Relay_Log_Space: 1891
              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: 8e7dddd2-34e9-11ee-84b0-fa163ee79023
             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