欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-21 12:09:53
...
bitsCN.com

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

配置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配置

[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  


bitsCN.com
相关标签: database 数据库