01 Jul 2009

MySQL 备份恢复同步一条龙

一 .概要

以某个slave 为基准，拷贝数据到其它slave，然后从 master 开始同步。

二 .导出过程

这里 以 db01 为基准， 以 db46 为 master

1. 记录 db01 mysql  show  slave status 的  “Master_Log_File”,”Read_Master_Log_Pos” 两项信息

1)   show slave status \G;

1. 从 db01 逐个库导出

DBS=”AhtycpDNS Buy FastLottery Lottery NewOkCom OlympicLottery Statistic UserInfo  okooo_admin proftpd static  voice”

1. 然后逐个库导入目标 slave，记录操作耗时等信息

DBS=”AhtycpDNS Buy FastLottery Lottery NewOkCom OlympicLottery Statistic UserInfo  okooo_admin proftpd static  voice”

4.为slave授权

grant replication slave on *.* to 'rep57'@'xxx' identified by 'xxx';

三.导入流程

1.my.cnf文件，做红色标记的是为需要注意的地方。

[client]
port    = 3306
socket  = /tmp/mysql.sock

[mysqld] default-character-set = latin1 user = okooo port = 3306 socket = /tmp/mysql.sock basedir = /home/okooo/apps/mysql/ datadir = /home/okooo/apps/mysql/data open_files_limit = 10240 back_log = 600 max_connections = 3000 max_connect_errors = 6000 table_cache = 614 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 300 thread_concurrency = 8 query_cache_size = 32M query_cache_limit = 2M query_cache_min_res_unit = 2k default-storage-engine = MyISAM default_table_type = MyISAM thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 246M max_heap_table_size = 246M long_query_time = 1 log_long_format expire_logs_days = 7 key_buffer_size = 256M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 30G myisam_repair_threads = 1 myisam_recover

skip-name-resolve

#start slave

server-id = 77 master-host = xxx master-user = rep57 master-password = xxx replicate-ignore-db=mysql skip-slave-start slave-skip-errors = 1032,1062,126,1114,1146,1048,1396 master-connect-retry = 10 master-retry-count = 100000000 slave_net_timeout = 20

#stop slave

innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2048M innodb_data_file_path = ibdata1:1024M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 32M</pre>

2.在启动 slave 时 ，设置 “Master_Log_File”,”Read_Master_Log_Pos” 这两项为第1步记录的值

1) CHANGE MASTER TO MASTER_LOG_FILE = ‘logbin.xxxxxxx’, MASTER_LOG_POS = xxxxxxxx ;

2) CHANGE MASTER TO MASTER_USER=’rep57′;

3) start slave;

1. 持续观察同步结果一段时间, 无误后做 一致性检查

show  slave status

1. 重复 3~5步，直至所有 slave 同步状态正常

下面开始是一个mysql主从配置的标准流程

1.在maste主机上，配置my.cnf

在[mysqld]配置段添加如下字段中增加

log-bin=mysql-bin.log

binlog-do-db=blog #要同步的数据库的名字

binlog-do-db=index #要同步的数据库的名字

server-id=1

重启mysql

#service mysqld restart

进入mysql中

#mysql -u root -ppassword

增加同步帐号

mysql>grant replication slave on *.* to ‘repication’@’%’ identified by ‘password’;

mysql>use database;

mysql>flush tables with read lock;

mysql>show master status:

———————————————————————

+——————+———-+——————+——————+

+——————+———-+——————+——————+

此时不要关闭此终端！

记住上表前两个字段的信息 mysql-bin.000002 和 1448 。

另开一个终端：

拿到主数据库上的“数据库快照”

#tar -czvf database.tar.gz /data/database

切换到上一个终端

mysql>unlock tables; #解锁，让主数据库192.168.1.10上的数据库正常更新

2.配置slave数据库

首先创建数据库

#mysql -uroot -ppassword

mysql>create database databasename #建一个与主服务器的数据库名字相同的数据库

mysql>exit;

3.在两个从数据库的[mysqld]配置段添加如下字段中增加

# 192.168.1.11

server-id=2

master-host=192.168.1.10

master-user=replication

master-password=password

master-connect-retry=60

replicate-do-db=blog

replicate-do-db=index

#192.168.1.12

server-id=3

master-host=192.168.1.10

master-user=replication

master-password=password

master-connect-retry=60

replicate-do-db=blog

replicate-do-db=index

3.分别重启两个数据库

#service mysqld restart

4.分别进入两个从数据库中

在salve2上

#mysql -uroot -ppassword

mysql>slave stop;

CHANGE MASTER TO

MASTER_HOST=’192.168.1.10′,

MASTER_USER=’replication’,

MASTER_PASSWORD=’password’,

MASTER_LOG_FILE=’mysql-bin.000002′,

MASTER_LOG_POS=1448;

MYSQL>START SLAVE;

在salve2上

#mysql -uroot -ppassword

mysql>slave stop;

CHANGE MASTER TO

MASTER_HOST=’192.168.1.10′,

MASTER_USER=’replication’,

MASTER_PASSWORD=’password’,

MASTER_LOG_FILE=’mysql-bin.000002′,

MASTER_LOG_POS=1448;

MYSQL>START SLAVE;

在slave端 ：

mysql> show processlist;

+—-+————-+———–+——+———+————+———————————————————————–+——————+

+—-+————-+———–+——+———+————+———————————————————————–+——————+

+—-+————-+———–+——+———+————+———————————————————-

以上信息表示同步成功！

4.同步测试过程

分别在主数据库上建立一个表，插入一条数据

在两个从数据库上看看是否更新

过程略。