mysql主从复制相关

最新推荐文章于 2023-04-27 11:34:20 发布

qw_xingzhe

最新推荐文章于 2023-04-27 11:34:20 发布

阅读量761

点赞数

分类专栏： MySQL

MySQL 专栏收录该内容

62 篇文章

订阅专栏

MySQL主从复制配置(旧版)

Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

(1)首先确保主从服务器上的Mysql版本相同

(2)在主服务器上,设置一个从数据库的账户,使用REPLICATION SLAVE赋予权限,如:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave001'@'192.168.0.99' IDENTIFIED BY

'123456';

Query OK, 0 rows affected (0.13 sec)

(3)修改主数据库的配置文件my.cnf,开启BINLOG，并设置server-id的值，修改之后必须重启Mysql服务

[mysqld]

log-bin = /home/mysql/log/mysql-bin.log

server-id=1

(4)之后可以得到主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 243

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

(5)好了，现在可以停止主数据的的更新操作，并生成主数据库的备份，我们可以通过mysqldump到处数据到从数据库，当然了，你也可以直接用cp命令将数据文件复制到从数据库去

注意在导出数据之前先对主数据库进行READ LOCK，以保证数据的一致性

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.19 sec)

之后是mysqldump

mysqldump -h127.0.0.1 -p3306 -uroot -p test > /home/chenyz/test.sql

最好在主数据库备份完毕，恢复写操作

mysql> unlock tables;

Query OK, 0 rows affected (0.28 sec)

(6)将刚才主数据备份的test.sql复制到从数据库，进行导入

(7)接着修改从数据库的my.cnf,增加server-id参数,指定复制使用的用户,主数据库服务器的ip,端口以及开始执行复制日志的文件和位置

[mysqld]

server-id=2

log_bin = /var/log/mysql/mysql-bin.log

master-host =192.168.1.100

master-user=test

master-pass=123456

master-port =3306

master-connect-retry=60

replicate-do-db =test

(8)在从服务器上,启动slave进程

mysql> start slave;

(9)在从服务器进行show salve status验证

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: root

Master_Port: 3306

Connect_Retry: 3

Master_Log_File: mysql-bin.003

Read_Master_Log_Pos: 79

Relay_Log_File: gbichot-relay-bin.003

Relay_Log_Pos: 548

Relay_Master_Log_File: mysql-bin .003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(10)好了,现在可以在我们的主服务器做一些更新的操作,然后在从服务器查看是否已经更新

----------------------------------------

陈于喆
Mail: chenyz@corp.netease.com

最近这一两个月业余时间基本都在看MySQL的书，看明白了一些，又忘了一些，哈哈，总的来说没有什么很大的收获
今天无聊了，弄个Replication(http://dev.mysql.com/doc/refman/5.1/en/replication.html)来玩玩
MySQL的Replication可以用在很多地方，比如平衡负载，备份等等
主要它复制的时候是一个异步的过程(有没有将随机读写变成顺序读写这个值得研究，对于大多数磁盘来说，顺序读写可能更快些)，可以从某些程度上来说提高性能，当然生成Binary-log也是有代价的，不过会换来更多的好处(在网络传输影响很小的情况下)，比如读写分离将读压力分出去了，比如多机备份

也有很多人写类似的文章和书籍，我也就记录个实践的过程

我这里实践所有的环境都是Linux 2.6.35-28-generic #50-Ubuntu SMP Fri Mar 18 19:00:26 UTC 2011 i686 GNU/Linux，两个数据库，一个5.1.57-log作为Master，一个5.5.8作为Slave，都装在一台机器上，启用不同的配置和端口
目的就想在Master插入一条数据，在Slave也能看到，性能以及安全的配置都没有考虑

看官方配置说明的时候就需要弄清楚哪些是在Master上配的，哪些是在Slave上配的

因为我的Slave是5.5，以前的版本，比如5.1中可用的
master-user = repl
master-password = repl
master-port = 3307
这些配置文件参数在5.5的Slave就不能用了，如果你不小心配置了这些参数，MySQL服务器将无法正常启动
看下MySQL日志你应该会发现类似于这样的提示(unknown variable 'master-host=')
2121 110809 22:53:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2122 110809 22:53:21 [Note] Plugin 'FEDERATED' is disabled.
2123 InnoDB: The InnoDB memory heap is disabled
2124 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2125 InnoDB: Compressed tables use zlib 1.2.3
2126 110809 22:53:21 InnoDB: Using Linux native AIO
2127 110809 22:53:21 InnoDB: Initializing buffer pool, size = 128.0M
2128 110809 22:53:21 InnoDB: Completed initialization of buffer pool
2129 110809 22:53:21 InnoDB: highest supported file format is Barracuda.
2130 110809 22:53:21 InnoDB: 1.1.4 started; log sequence number 42505789
2131 110809 22:53:21 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'master-host=127.0.0.1'
2132 110809 22:53:21 [ERROR] Aborting
2133
2134 110809 22:53:21 InnoDB: Starting shutdown...
2135 110809 22:53:22 InnoDB: Shutdown completed; log sequence number 42505789
2136 110809 22:53:22 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
2137
2138 110809 22:53:22 mysqld_safe mysqld from pid file /usr/local/mysql/data/KNIGHT.pid ended

那么在5.5的Slave中我是先配置了这几个参数
replicate-do-db = replication
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
有意的指定和排除了一些库参与同步
之后启动Slave，然后通过change master to master_host='127.0.0.1', master_port=3307, master_user='repl', master_password='repl', master_log_file='master-cutoe-binlog.000001', master_log_pos=600, master_connect_retry=10;
slave start;
来实现Slave的开始复制

Master配置文件主要参数如下：
server-id = 1
log-bin = master-cutoe-binlog
binlog_format = mixed
sync_binlog = 1
innodb_flush_log_at_trx_commit = 2

Slave配置文件主要参数如下：
server-id = 2
replicate-do-db = replication
replicate-ignore-db = mysql
replicate-ignore-db = information_schema

如果配置都对了的话，先起Master，再起Slave
在Master和Slave中创建好需要复制的库和表，比如
create database replication;
create table user(id int primary key auto_increment, name varchar(20), age smallint);
(让复制自动在Slave中创建和Master一样的表在这里我没有尝试，有空可以试试)
还要在Master中创建一个Slave用来访问Master的用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; --为什么不能指定该用户只对单独的库具有复制权限，目前不清楚原因
然后在Master中mysql> show master status;
在Slave中通过change master to，slave start开启复制
并mysql> show slave status \G
就应该会看到Slave_IO_State: Waiting for master to send event
这个时候再在Master中执行个Insert语句，如果没有错误的话，应该可以在Slave的相应表中看到刚刚你在Master插入的一样的数据
基本可以通过mysql> show slave status \G查看到复制过程中发生的所有情况，例如错误信息

如果Slave复制失败，你可以根据错误信息进行修正，然后执行
mysql> slave stop;
mysql> slave start;
就可以把原来应该复制过来的数据都复制过来

别忘了一个很简单的命令也是要常用的
mysql> show processlist \G

Good Luck & Good Night...

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

首先确认您的再slave服务器上执行了如下，来配置slave服务器。

change master to master_host='master_host', master_user='you_user_name', master_password='you_master_user_password',master_log_file='mysqld-bin.000001', master_log_pos=98;

（master_log_file和master_log_pos）的值你可以在服务器上运行 show master status; 来得到。

接下来确认slave和master的上的server_id是否正确。可以分别在slave和master上运行 SHOW VARIABLES LIKE 'server_id'; 来查看server_id是否和你配置的一样。

不一样修改配置文件。可以直接从 /use/share/mysql 下拷贝相关的配置文件。

祝您成功。

mysql Slave_IO_Running:NO（解决方法)

Master slave 复制错误

Description：

Slave_IO_Running:NO

Slave_SQL_Running:Yes

Seconds_Behind_Master: NULL

本人遇到的Slave_IO_Running:NO的情况有下面两种：

1. 在配置slave同步时因为slave访问master没有权限导致；

2. master上的mysql-bin.xxxxxx文件全被我误删除了；

对于第一种情况，仔细检查数据库访问权限即可解决；

对于第二种情况，下面稍微详细介绍一下：

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.3.21

Master_User: slave

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000016

Read_Master_Log_Pos: 173

Relay_Log_File: mysqld-relay-bin.000008

Relay_Log_Pos: 98

Relay_Master_Log_File: mysql-bin.000016

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table: br> Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 173

Relay_Log_Space: 98

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: NULL

1 row in set (0.00 sec)

[root@slave mysql]# tail /var/log/mysqld.log

081223 15:51:50 InnoDB: Started; log sequence number 0 43655

081223 15:51:51 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when

this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-

log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.

081223 15:51:51 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.45-log' socket: '/var/lib/mysql/mysql.sock' port: 3307 Source distribution

081223 15:51:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000016' at

position 173, relay log '/var/run/mysqld/mysqld-relay-bin.000007' position: 98

081223 15:51:51 [Note] Slave I/O thread: connected to master

slave@192.168.3.21:3307''>'slave@192.168.3.21:3307'

, replication started

in log 'mysql-bin.000016' at position 173

081223 15:51:51 [ERROR] Error reading packet from server: Could not find first log file name in binary log

index file ( server_errno=1236)

081223 15:51:51 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file'

from master when reading data from binary log

081223 15:51:51 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000016', position 173

081223 15:51:58 [Note] Error reading relay log event: slave SQL thread was killed

解决步骤：

重启master库：service mysqld restart

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 98 | | |

+------------------+----------+--------------+------------------+

mysql> slave stop;

mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;

mysql> slave start;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.21

Master_User: slave

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

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: 98

Relay_Log_Space: 235

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

1 row in set (0.00 sec)