我的mysql主从复制配置方法

server-id我用了IP的最后一个数字。日志最大512MB，删除30天以前的记录，可以根据实际环境自由调整。

第一步：

主mysql my.cnf配置：

写道

server-id = 228
log-bin=mysql-bin
expire_logs_days = 30

 

 

连接主数据库：

mysql -uroot -pxxxxxxxx

GRANT REPLICATION SLAVE ON *.* to 'sync_xxx'@'%' identified by 'xxxxxxxx';

flush privileges;

查看权限：select * from user where user = 'sync_xxx';

可以看到Repl_slave_priv项对就的值为Y。

 

 

 第二步：

从数据库配置my.cnf并重启：

写道

server-id = 220
log-bin=mysql-bin
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
replicate-wild-ignore-table =mysql.%
replicate-wild-ignore-table =test.%
replicate-wild-ignore-table =log.%
replicate-wild-ignore-table =information_schema.%
replicate-wild-ignore-table =performance_schema.%

 

 

第三步，也是最重要最要紧的一步，要保证两个数据库数据完全一致，必须停掉主库的web服务，然后dump数据，然后启动主数据库，查看日志状态，因为这份备份的数据导入从库，日志状态配置到从库，可以保证从库数据和主库完全一致。

 

此时必须保证两个数据库都没有读写操作（断开web等写数据库的服务）。

dump主数据库数据（如果数据太大不能dump，可以停止掉mysql，把整个数据目录拷贝过去。），然后查看日志状态：

1、连接数据库：

查看日志状态：show master status;看到如下信息。

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |     1230 |              |                  |

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

 

1 row in set (0.00 sec)

有了备份文件，有了备份文件以后没有进行任何写操作的主数据库的master状态，那么主数据库所在服务器的web服务就可以恢复了，因为从库导入了相同数据，且可以配置这个日志起点。 

 

主库导出的数据导入到从数据库。

连接到从数据库，执行：注意：mysql-bin.000002来自查看主库状态得到的文件名字段File，MASTER_LOG_POS是Position字段。

CHANGE MASTER TO

MASTER_HOST='192.168.1.228',

MASTER_USER='xxxxx',

MASTER_PASSWORD='xxxxxx.',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=1230,

MASTER_CONNECT_RETRY=10;

执行：

start slave;

 

查看从数据库状态：show slave status\G;

 

 

配置中已经设置从数据库为只读，防止从数据库写入任何数据，检查一下：

mysql> show global variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.01 sec)

 

这里要注意，虽然设置了只读，但root用户仍然有权限写入数据，所以所有写数据库的服务都不可以用root用户来连接，另外，如果创建用户的时候，是如下方法，也会导致创建了拥有supper权限的root类用户：

 

grant all privileges on *.* to username@127.0.0.1 identified by 'password';

flush privileges;

相当于创建了另一个超级用户！！！

所以，创建用户只能用如下方法：

grant all privileges on db_name.* to username@127.0.0.1 identified by 'password';

flush privileges;

验证设置只读成功的方法：

连接mysql数据库

更新一条数据看看能不能成功。

mysql> update account set nick='xxx';

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

 

主从配置完毕，去主库变更一个数据，然后从库执行sql查询看看有没有随之变化吧，如果变化了，就启动主从服务器上的web服务即可。

 

 

 主从配置的作用主要是做到双机热备，但对外提供服务的只有主数据库，当然，从数据库可用于数据读取，但绝对不能有写入操作，否则从数据库数据是不会同步到主数据库中的，而且对于采用自动递增id的情况，会导致从数据库因为添加了数据，而无法从主数据库从不过来（id重复的错误）。

如果主从数据库都要对外提供服务，则需要相互配置主从数据库，可以多台主机互为主从，但需要设置id的递增方法，见文章：

http://www.cnblogs.com/top5/archive/2009/04/15/1436399.html