前提会使用linux
master:192.168.70.101
slave:192.168.70.100
1,本文章使用的mysql版本如下:
[root@oracle mysql]# rpm -qa|grep MySQL
MySQL-server-community-5.1.56-1.rhel5MySQL-client-community-5.1.56-1.rhel5MySQL-devel-community-5.1.56-1.rhel5接着在两台主机上,用rpm -ivh “rpm包”安装软件包。
然后修改mysql数据库的密码为'mysql';
2,登陆mysql数据库
[root@oracle mysql]# mysql -uroot -pmysql
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.1.56-community-log MySQL Community Server (GPL)Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement. 在master上创建用户repl;mysql>create user repl;mysql>grant replication slave on *.* to 'repl'@'192.168.70.101' identified by 'repl';
在master上my.cnf配置如下:
[mysqld]
datadir=/var/lib/mysqluser=mysqlport=3306innodb_data_home_dir = /var/lib/mysqlinnodb_data_file_path = ibdata1:100M:autoextendserver-id=2log-bin=master-binlog-bin-index=master-bin.indexsync_binlog = 1innodb_flush_log_at_trx_commit=1innodb_support_xa = 1binlog_format = rowdefault-storage-engine=innobasecharacter-set-server=utf8innodb_buffer_pool_size=1Ginnodb_additional_mem_pool_size=20Minnodb_log_buffer_size=8Mslow_query_log=onlog_error=/var/lib/mysql/master_server.errsql_mode=strict_trans_tableslog-slow-admin-statementslog-queries-not-using-indexesslow_query_log=onslow_query_log_file=/var/lib/mysql/master_slow_log.log
在slave的my.cnf中配置如下:
[mysqld]
datadir=/var/lib/mysqlport=3306user=mysqldefault-storage-engine=innodbdefault-table-type=innodbinnodb_data_home_dir = /var/lib/mysqlinnodb_data_file_path = ibdata1:15m:autoextendinnodb_log_group_home_dir=/var/lib/mysql/innodb_log/server-id=3log_slave_updates=1read_only=onskip_slave_startlog-bin=slave-binrelay_log=slave-relay-binrelay-log-index=slave-relay-bin.indexbinlog_format = rowdefault-storage-engine=InnoDBcharacter-set-server=utf8innodb_buffer_pool_size=1Ginnodb_additional_mem_pool_size=20Minnodb_log_buffer_size=8Mslow_query_log=onlog_queries_not_using_indexes=1sql_mode=strict_trans_tableslog-slow-admin-statementslog-queries-not-using-indexesslow_query_log=onslow_query_log_file=/var/lib/mysql/slave_slow_log.log
在master上产看,
mysql> show master status;+-------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 | 106 | | |+-------------------+----------+--------------+------------------+1 row in set (0.00 sec)在slave上使用change master to命令将slave指向master。然后使用start slave命令启动复制。
mysql> change master to
->master_host='192.168.70.101',
->master_port=3306,
->master_user='repl',
->master_password='repl',
->master_log_file='master-bin.000003',
->master_log_pos=106;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.70.101 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 106 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 252 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes ---成功 Slave_SQL_Running: Yes ---成功
flush logs命令强制轮换二进制日志,从而可以得到完整的二进制日志文件。
使用show binlog events in 'master-bin.000004(二进制日志)'\G;
检查二进制日志里有哪些事件
mysql> show binlog events in 'master-bin.000004'\G;
*************************** 1. row *************************** Log_name: master-bin.000004 Pos: 4 Event_type: Format_desc Server_id: 2End_log_pos: 106 Info: Server ver: 5.1.56-community-log, Binlog ver: 4*************************** 2. row *************************** Log_name: master-bin.000004 Pos: 106 Event_type: Query Server_id: 2End_log_pos: 200 Info: use `test`; create table tb1(text char(10))*************************** 3. row *************************** Log_name: master-bin.000004 Pos: 200 Event_type: Rotate Server_id: 2End_log_pos: 244 Info: master-bin.000005;pos=43 rows in set (0.00 sec)
//克隆MASTER
[root@server picture]# mysqldump -uroot -pmysql --host=192.168.70.101 --all-databases --master-data=1 > backup-source.sql
--master-data=1选项mysqldump写change master to 语句,且参数为二进制日志文件及其位置。
然后在slave上恢复备份:
[root@server picture]#mysql -uroot -pmysql --host=192.168.70.100 < backup-source.sql
//克隆SLAVE
//清除Binlog日志
服务器自动清理旧的binlog文件,需设置expire-logs-days选项,这个选项可以作为服务器变量。如果服务重启后,不受影响,需要在my.cnf设置。
使用purge binary log命令手工清除binlog文件。格式如下:
1,purge binary log before datatime
将清除在给定时间之前的所有文件。
2,purge binary logs to 'filename'
将清除在给定文件之前的所有文件。
//默认情况下,由slave执行的事件没有被记录到二进制日志中,如果这个slave是master的一个备份,这时会出现问题。
在my.cnf添加log-slave-updates,以确保来自于master并被执行的语句会被写入slave的二进制日志中。
切换基本思路:为了让slave赶上备份服务器,并在正确的位置停止,使用start slave until命令。
slave>start slave until master_log_file='master-bin-000006',master_log_pos=700;
slave>select master_pos_wait('master-bin-000006',700);