mysql主从配置

最新推荐文章于 2022-09-02 14:15:31 发布

转载最新推荐文章于 2022-09-02 14:15:31 发布 · 899 阅读

文章标签：

#mysql #主从 #配置 #主从配置

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了MySQL主从复制的配置与实现步骤，包括创建root用户、配置my.cnf、启动主从同步等关键操作，确保数据库系统的高可用性。

输入以下SQL语句，创建root用户（root）和密码（new password）：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'new password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'new password';

授权给从数据库服务器192.168.1.xxx(从服务器IP地址)
mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.1.xxx' identified by ‘password’;

查询主数据库状态
mysql> show master status;
Empty set (0.00 sec)

这里空，说明没有。

我这里配置错误了。错误的原因是将master的配置放在
[mysqld]的下面，我却放在了[mysqld_safe]的下面所以没有成功。

成功后的结果如下：

mysql的my.cnf的配置：

[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=root
skip-locking
key_buffer = 256M
max_allowed_packet = 8M
table_cache = 1024
sort_buffer_size = 64M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M

default-table-type=innodb
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 128M
innodb_flush_log_at_trx_commit = 0
#innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

long_query_time = 1
log-slow-queries = /var/log/mysql/log-slow.log
skip-name-resolve
log_bin_trust_routine_creators=1
max_connections = 2048
query_cache_size = 128M
record_buffer = 32M

back_log = 500
#interactive_timeout = 7200
interactive_timeout = 2880000
thread_cache_size = 80
#wait_timeout = 720
wait_timeout = 2880000
max_connect_errors=100
tmp_table_size = 512M
log = /var/log/mysql/query.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire-logs_days =10
log-slave-updates
bind-address = 0.0.0.0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

好了。主数据库完成配置。

下面配置从服务器：

配置my.cnf

[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=root
skip-locking
key_buffer = 256M
max_allowed_packet = 8M
table_cache = 1024
sort_buffer_size = 64M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M

default-table-type=innodb
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 128M
innodb_flush_log_at_trx_commit = 0
#innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

long_query_time = 1
log-slow-queries = /var/log/mysql/log-slow.log
skip-name-resolve
log_bin_trust_routine_creators=1
max_connections = 2048
query_cache_size = 128M
record_buffer = 32M

back_log = 500
#interactive_timeout = 7200
interactive_timeout = 2880000
thread_cache_size = 80
#wait_timeout = 720
wait_timeout = 2880000
max_connect_errors=100
tmp_table_size = 512M
log = /var/log/mysql/query.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id = 2 #从服务器ID号
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启mysql并进入mysql

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'new password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'new password';

mysql> change master to
master_host=’192.168.1.xxx’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000001’,
master_log_pos=106;

正确执行后启动Slave同步进程
mysql> start slave;

主从同步检查
mysql> show slave status\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.161.136.210
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        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: 106
              Relay_Log_Space: 407
              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:
1 row in set (0.00 sec)

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态
mysql> show master status;

(3)记录下 FILE 及 Position 的值。
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
mysql> UNLOCK TABLES;

验证主从复制效果

主服务器上的操作
在主服务器上创建数据库first_db
mysql> create database first_db;
Query OK, 1 row affected (0.00 sec)

mysql> use first_db;
Database changed
mysql> create table first_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into first_tb values(001,'myself');
Query OK, 1 row affected (0.00 sec)

在从服务器上查看
mysql> show databases;

mysql> use first_db
Database chaged

mysql> show tables;
=============================
+--------------------+
| Tables_in_first_db |
+--------------------+
| first_tb |
+--------------------+
1 row in set (0.02 sec)
=============================
数据库表first_tb也已经自动创建

mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
记录也已经存在

由此，整个MySQL主从复制的过程就完成了.