Mysql 5.5 replication 多数据库主从备份Master-Slave配置总结
来源:互联网 发布:阿里云手机备份 编辑:程序博客网 时间:2024/06/05 11:49
配置Mysql server 5.5 的双机备份,也就是master-slave模式。本例子还是一个多database复制的情况。
现在有两个database在同一台mysql 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配置
[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]port = 3306socket = /var/lib/mysql/mysql.sockskip-external-lockingkey_buffer = 384Mmax_allowed_packet = 32Mtable_open_cache = 5120key_buffer_size = 64Msort_buffer_size = 512Mnet_buffer_length = 8Kread_buffer_size = 16Mread_rnd_buffer_size = 128Mmyisam_sort_buffer_size = 128Mquery_cache_size = 256Mtmp_table_size = 128Mmax_heap_table_size = 128Mthread_concurrency = 8max_connections=500group_concat_max_len=1048576max_sp_recursion_depth=255slow_query_log_file=/spare/mysql/slow_queries.loglong_query_time = 5binlog_cache_size = 2Mjoin_buffer_size = 32Mthread_cache_size = 16query_cache_limit = 2Mtransaction_isolation = REPEATABLE-READlog-bin=mysql-binauto_increment_increment = 2auto_increment_offset = 1server-id = 1binlog-do-db = ADBbinlog-do-db = BDBlog_slave_updates = 1relay-log=RELAY_LOCALHOST-relay-bininnodb_data_home_dir = /spare/mysql/innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir = /spare/mysql/innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 8Ginnodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 8Minnodb_log_file_size = 250Mmax_binlog_size=100Mexpire_logs_days=3sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely mannerbinlog-format=ROW[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehash[isamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout
slave my.cnf配置
[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]port = 3306socket = /var/lib/mysql/mysql.sockskip-external-lockingkey_buffer = 384Mmax_allowed_packet = 32Mtable_open_cache = 5120key_buffer_size = 64Msort_buffer_size = 512Mnet_buffer_length = 8Kread_buffer_size = 16Mread_rnd_buffer_size = 128Mmyisam_sort_buffer_size = 128Mquery_cache_size = 256Mtmp_table_size = 128Mmax_heap_table_size = 128Mthread_concurrency = 8max_connections=500group_concat_max_len=1048576max_sp_recursion_depth=255slow_query_log_file=/spare/mysql/slow_queries.loglong_query_time = 5binlog_cache_size = 2Mjoin_buffer_size = 32Mthread_cache_size = 16query_cache_limit = 2Mtransaction_isolation = REPEATABLE-READlog-bin=mysql-binauto_increment_increment = 2auto_increment_offset = 1 # the slave offset keep same as masterserver-id = 2replicate-do-db = ADBreplicate-do-db = BDBlog_slave_updates = 1relay-log=RELAY_LOCALHOST-relay-bininnodb_data_home_dir = /spare/mysql/innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir = /spare/mysql/innodb_additional_mem_pool_size = 16Minnodb_autoextend_increment = 256Minnodb_buffer_pool_size = 8Ginnodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 8M####innodb_log_buffer_size = 32Minnodb_log_file_size = 250Mmax_binlog_size=100Mexpire_logs_days=3sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely mannerbinlog-format=ROW[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehash[isamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout
- Mysql 5.5 replication 多数据库主从备份Master-Slave配置总结
- Mysql 5.5 主从数据复制(Master-Slave Replication)配置笔记
- MySQL Replication,主从同步( Master-Slave)配置流程
- MySQL Master Slave Replication配置
- [MYSQL] LINUX下配置mysql双机备份 master/slave主从
- mysql 主从配置(master/slave)
- MySQL Master-Slave Replication
- MySQL Master-Slave Replication
- Mysql主从备份实现master-slave
- 重置mysql主从同步(MySQL Reset Master-Slave Replication)
- MySql 5.7 主从库配置方案(Master/Slave)
- linux配置mysql主从--master and slave
- 多数据库 master-slave 不同步问题总结
- 搭建mysql master-slave replication
- centos6,mysql 5.5 配置 master \ slave 数据同步备份
- mysql master-slave备份配置文档
- Mysql 复制 配置详解, Replication with Master/Slave
- MySQL Replication(Master与Slave基本原理及配置)
- 忙
- PHP函数之error_reporting(E_ALL ^ E_NOTICE)详细说明 详细出处参考:http://www.jb51.net/article/27560.htm
- 浅析上好中职计算机课程的教学技巧
- 图-代权最小树
- 列出磁盘上的所有分区
- Mysql 5.5 replication 多数据库主从备份Master-Slave配置总结
- 大型网站运维探讨和心得
- QSpinBox子类化一例(进制可变的SpinBox)
- 修改ROM设置默认参数,SettingsProvider\res\values\bools:
- Uncaught SyntaxError: Unexpected identifier
- POJ_2992_divisors
- Oracle进程详解
- MySQL:给表建立索引及索引的显示
- Linux 用户态与内核态的交互