Mysql 5.5 replication多数据库主从备份Master-Slave配置

页面导航:首页 > 数据库 > Mysql > Mysql 5.5 replication多数据库主从备份Master-Slave配置

Mysql 5.5 replication多数据库主从备份Master-Slave配置

来源: 作者: 时间:2016-02-04 09:03 【

Mysql 5 5 replication多数据库主从备份Master-Slave配置总结配置Mysql server 5 5 的双机备份,也就是master-slave模式。本例子还是一个多database复制的情况。现在有两个database在同一台mysql server,
Mysql 5.5 replication多主从备份Master-Slave配置总结
 
配置Mysql server 5.5 的双机备份,也就是master-slave模式。本例子还是一个多database复制的情况。
 
现在有两个database在同一台 server,也就是master,各自有自己的user访问和操作,用于不同的应用程序。这两个database都要通过replication配置,实时复制到另一台mysql server上,也就是slave。
 
配置步骤:
 
1. 从master上到处现有的数据
mysqldump -R -E -uroot -p ADB > masteradb.sql;
mysqldump -R -E -uroot -p BDB > masterbdb.sql;

 

 
2. 为master和slave定制配置文件/etc/my.cnf,然后启动mysql
 
vi /etc/my.cnf
 
###详细内容见附录
service mysql restart
 
3. 在slave上创建database.
create database ADB;
create database BDB;
 
4. 在master和slave上配置user和privilege;
 
GRANT ALL PRIVILEGES ON ADB.* TO usera@`%` IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON ADB.* TO usera@`localhost` IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON BDB.* TO userb@`%` IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON BDB.* TO userb@`localhost` IDENTIFIED BY 'pass' WITH GRANT OPTION;
flush privileges;

 

 
5. 在master上配置用于数据复制的user和privilege;
 
grant SUPER ON *.* TO 'auser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
grant SUPER ON *.* TO 'buser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT SELECT ON mysql.proc TO 'auser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT SELECT ON mysql.proc TO 'buser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO repluser IDENTIFIED BY 'pass';
flush privileges;

 

 
6. 在master上运行下列命令,得到master的状态,包含binlog的文件名和当前位置
mysql> show master status;
+------------------+----------+--------------------------------+------------------+
| File             | Position | Binlog_Do_DB                   | Binlog_Ignore_DB |
+------------------+----------+--------------------------------+------------------+
| mysql-bin.000001 |   000173| ADB,BDB |                  |
+------------------+----------+--------------------------------+------------------+
1 row in set (0.02 sec)

 

 
7. 在slave配置复制来源,包括hostname,user,password,binlog文件名,位置。
CHANGE MASTER TO MASTER_HOST='10.224.106.225', MASTER_USER='repluser', MASTER_PASSWORD='pass', master_log_file='mysql-bin.000001', master_log_pos=173;
start slave;

 

 
8. 运行下列命令查看slave的当前状态。
 
show slave status\G
 
结果出现
 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 
表示数据同步复制已经成功运行。
 
提示:如果show slave status\G 出现了错误,运行下列命令跳过,在重启slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER =100;start slave;
show slave status\G;
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER =0;start slave;

 

 
附录:
 
master my.cnf配置
 
[plain] 
[client]  
port            = 3306  
socket          = /var/lib/mysql/mysql.sock  
  
  
[mysqld]  
port            = 3306  
socket          = /var/lib/mysql/mysql.sock  
skip-external-locking  
key_buffer = 384M  
max_allowed_packet = 32M  
table_open_cache = 5120  
key_buffer_size = 64M  
sort_buffer_size = 512M  
net_buffer_length = 8K  
read_buffer_size = 16M  
read_rnd_buffer_size = 128M  
myisam_sort_buffer_size = 128M  
  
query_cache_size = 256M  
tmp_table_size = 128M  
max_heap_table_size = 128M  
thread_concurrency = 8  
max_connections=500  
group_concat_max_len=1048576  
  
max_sp_recursion_depth=255  
  
  
slow_query_log_file=/spare/mysql/slow_queries.log  
long_query_time = 5  
  
  
  
binlog_cache_size = 2M  
join_buffer_size = 32M  
thread_cache_size = 16  
query_cache_limit = 2M  
transaction_isolation = REPEATABLE-READ  
log-bin=mysql-bin  
auto_increment_increment = 2  
auto_increment_offset = 1  
server-id       = 1  
  
binlog-do-db            = ADB  
binlog-do-db            = BDB  
  
log_slave_updates = 1  
relay-log=RELAY_LOCALHOST-relay-bin  
  
  
  
innodb_data_home_dir = /spare/mysql/  
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend  
innodb_log_group_home_dir = /spare/mysql/  
  
innodb_additional_mem_pool_size = 16M  
  
innodb_buffer_pool_size = 8G  
innodb_thread_concurrency = 16  
  
innodb_flush_log_at_trx_commit = 2  
  
innodb_log_buffer_size = 8M  
  
innodb_log_file_size = 250M  
  
max_binlog_size=100M  
expire_logs_days=3  
sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely manner  
binlog-format=ROW  
  
[mysqldump]  
quick  
max_allowed_packet = 256M  
  
[mysql]  
no-auto-rehash  
  
[isamchk]  
key_buffer = 256M  
sort_buffer_size = 256M  
read_buffer = 2M  
write_buffer = 2M  
  
[myisamchk]  
key_buffer = 256M  
sort_buffer_size = 256M  
read_buffer = 2M  
write_buffer = 2M  
  
[mysqlhotcopy]  
interactive-timeout  


slave my.cnf配置
[plain] 
[client]  
port            = 3306  
socket          = /var/lib/mysql/mysql.sock  
  
  
[mysqld]  
port            = 3306  
socket          = /var/lib/mysql/mysql.sock  
skip-external-locking  
key_buffer = 384M  
max_allowed_packet = 32M  
table_open_cache = 5120  
key_buffer_size = 64M  
sort_buffer_size = 512M  
net_buffer_length = 8K  
read_buffer_size = 16M  
read_rnd_buffer_size = 128M  
myisam_sort_buffer_size = 128M  
  
query_cache_size = 256M  
tmp_table_size = 128M  
max_heap_table_size = 128M  
thread_concurrency = 8  
max_connections=500  
group_concat_max_len=1048576  
  
max_sp_recursion_depth=255  
  
  
slow_query_log_file=/spare/mysql/slow_queries.log  
long_query_time = 5  
  
  
  
binlog_cache_size = 2M  
join_buffer_size = 32M  
thread_cache_size = 16  
query_cache_limit = 2M  
transaction_isolation = REPEATABLE-READ  
log-bin=mysql-bin  
auto_increment_increment = 2  
auto_increment_offset = 1   # the slave offset keep same as master  
server-id       = 2  
  
replicate-do-db            = ADB  
replicate-do-db            = BDB  
  
log_slave_updates = 1  
relay-log=RELAY_LOCALHOST-relay-bin  
  
  
  
innodb_data_home_dir = /spare/mysql/  
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend  
innodb_log_group_home_dir = /spare/mysql/  
  
innodb_additional_mem_pool_size = 16M  
innodb_autoextend_increment = 256M  
innodb_buffer_pool_size = 8G  
innodb_thread_concurrency = 16  
  
innodb_flush_log_at_trx_commit = 2  
innodb_log_buffer_size = 8M  
####innodb_log_buffer_size = 32M  
innodb_log_file_size = 250M  
  
  
max_binlog_size=100M  
expire_logs_days=3  
sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely manner  
binlog-format=ROW  
  
[mysqldump]  
quick  
max_allowed_packet = 256M  
  
[mysql]  
no-auto-rehash  
  
[isamchk]  
key_buffer = 256M  
sort_buffer_size = 256M  
read_buffer = 2M  
write_buffer = 2M  
  
[myisamchk]  
key_buffer = 256M  
sort_buffer_size = 256M  
read_buffer = 2M  
write_buffer = 2M  
  
[mysqlhotcopy]  
interactive-timeout  

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<