mysql主从同步

最新推荐文章于 2025-06-02 18:06:23 发布

zzw_zhangzhiwei

最新推荐文章于 2025-06-02 18:06:23 发布

阅读量408

点赞数 2

分类专栏： mysql linux 文章标签： ubuntu linux mysql 数据库备份

本文链接：https://blog.youkuaiyun.com/zzwaini785452392/article/details/69498931

版权

mysql 同时被 2 个专栏收录

4 篇文章

订阅专栏

linux

3 篇文章

订阅专栏

1 ，准备两台服务器：A，10.0.2.81(主数据库) B，10.0.2.82(从数据库)

准备数据库和表

CREATE DATABASE `test`;CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

2，配置A中my.cnf

[mysqld]
server-id=1
log-bin=/home/mysql-bin/mysql-bin
binlog-do-db=test       # 需要备份的数据库名, 多个则重复设置

重启mysql：service mysqld restart

进入mysql ：mysql -uroot -p

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      886 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后添加用户test

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'test'@'10.0.2.82' IDENTIFIED BY '123456';

flush privileges;刷新权限

3，配置从服务器

[mysqld]
server-id               = 2
log-bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test

重启mysql：service mysqld restart

进入mysql服务器：mysql -uroot -p

mysql> stop slave; 
mysql> change master to 
    master_host='10.0.2.81', 
    master_port=3306, 
    master_user='test',   master_password='123456',    master_log_file='mysql-bin.000002',
    master_log_pos=886;

mysql> start slave;
mysql> show slave status

show slave status;
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 10.0.2.81 | test| 3306 | 60 | mysql-bin.000002 | 886 | mysqld-relay-bin.000004 | 210 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 886 | 1292 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.03 sec)

确保Slave_IO_Running和Slave_SQL_Running的状态都必须是YES

最后开始验证：在主数据库中插入一条数据 mysql> insert into test(id,name) values (1,'zhi');

登录从mysql服务器查看test表中数据

注意点：在配置过程中一定要在[mysqld]下，而且在建表中不能用mysql关键词