mysql5.0主从复制设置及常用维护命令

前提：

master已经运行很长时间了，用同样版本的mysql建立起slave。

第一步：

   建立复制账号

GRANT REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_pass';




如果想要在slave上有权限执行"LOAD TABLE FROM MASTER"或"LOAD DATA FROM MASTER"语句的话，必须授予全局

的FILE和SELECT权限(绝对不推荐）：

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO  'repl'@'192.168.0.163' IDENTIFIED BY 'rep';

 

 第二步：

确保master有如下配置：

[mysqld]
log-bin=mysql-bin
server-id=1
expire_logs_days=15

 

   为了保证数据的一致性和可靠性，可以加上以下选 项：

innodb_flush_log_at_trx_commit=1
sync_binlog=1 

 

第三步：

  确保slave有如下配置：

 

server-id=2
log-bin=mysql-bin
relay-log=relay-bin 
relay-log-index=relay-bin 
log_slave_updates=1
read_only=1
skip_slave_start 
replicate_wild_do_table=mydb.%

 

 

第四步：

    获取同步点，第一种方法：

    a

mysql> FLUSH TABLES WITH READ LOCK;

 

   不要退出客户端，否则读锁失效！！

  b

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

 

此时，仍然不要退出第四步的mysql clients

myisam：

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

 

     innodb：

shell> mysqldump --single-transaction --all-databases --master-data=2  > dbdump.db

 

 

 

获取数据后解锁的语句：

mysql> UNLOCK TABLES；

 

 

第五步:在slave上导入数据

 

     登录到slave，导入数据

 

第六步：开始复制

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
    ->     START SLAVE;

 

其它有用的选 项：

        replicate-do-db=backup
　　master-connect-retry=10

        binlog-do-db=backup

 

 

 

 

master端：
show master status;—查看状态：
show processlist; –查看slave下mysql进程信息
reset master; #慎用，将清空日志及同步position

set sql_log_bin=0

slave端：

CHANGE MASTER TO MASTER_LOG_FILE=’master.000019′;
show slave status;
show slave logs;
show processlist;
reset slave; #慎用，将清空slave配置信息、日志及同步position
在从服务器上跳过错误事件
mysql>stop slave;
mysql>set global sql_slave_skip_counter = n(跳过主服务器中的接下来的 n 个事件。此命令对于由语句引起的复制终止有效。仅在从服务器线程没运行的时候有效);
mysql>start slave;

mysql 主服务器中同步用户 必须具有 SUPER ，RELOAD，REPLICATION SLAVE 权限

当新加从服务器时，需要先在从库上 load data master; 保证和其他从库数据一致

 

set global sql_slave_skip_counter=n # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。

reset master #主机端运行,清除所有的日志，这条命令就是原来的flush master

reset slave #从机运行，清除日志同步位置标志，并重新生成master.info

虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，

load table tblname from master
#从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值

load data from master #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大， 要增加net_read_timeout 和 net_write_timeout的值

change master to master_def_list #在线改变一些主机设置，多个用逗号间隔,比如

change master to

master_host=’master2.mycompany.com’,

master_user=’replication’,

master_password=’bigs3cret’

master_pos_wait() #从机运行

show master status #主机运行，看日志导出信息

show slave hosts #主机运行，看连入的从机的情况。

show slave status (slave)

show master logs (master)

show binlog events [ in 'logname' ] [ from pos ] [ limit [offset,] rows ]

purge [master] logs to ‘logname’ ; purge [master] logs before ‘date’

//显示所有本机上的二进制日志
mysql> SHOW MASTER LOGS;
//删除所有本机上的二进制日志
mysql> RESET MASTER;
//删除所有创建时间在binary-log.xxx之前的 二进制日志
mysql> PURGE MASTER LOGS TO ‘binary-log.xxx’;
//只保留最近6天的日志，之前的都删掉
find /var/intra -type f -mtime +6 -name “*.log” -exec rm -f {} \;
//用键盘左上角（也就是Esc下面）那个键包围起来，说明是命令。-1d 是昨天，以此类推-1m是上个月等等
day=`/bin/date -v -1d +%Y%m%d`;
//给文件改名
mv xxx.log xxx-${day}.log;
//这里还要加上数据库的用户名密码，作用是更新日志（包括二进制日志和查 询日志等等）
mysqladmin flush-logs