Mysql利用percona-xtrabackup在线配置主从的方案
发布时间:2022-01-16 11:28:20 所属栏目:MySql教程 来源:互联网
导读:这篇文章主要讲解了Mysql利用percona-xtrabackup在线配置主从的方法,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习Mysql利用percona-xtrabackup在线配置主从的方法吧! Mysql 利用percona-xtrabackup在线配
这篇文章主要讲解了“Mysql利用percona-xtrabackup在线配置主从的方法”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“Mysql利用percona-xtrabackup在线配置主从的方法”吧! Mysql 利用percona-xtrabackup在线配置主从 一.在主从上分别安装Mysql 编译安装 yum -y install make gcc-c++ cmake bison-devel ncurses-devel groupadd mysql useradd -g mysql mysql mkdir -p /usr/local/mysql [root@TESTDB02-1-16 tools]# tar -xf mysql-5.6.27.tar.gz [root@TESTDB02-1-16 tools]# cd mysql-5.6.27 [root@TESTDB02-1-16 mysql-5.6.27]# pwd /usr/local/tools/mysql-5.6.27 [mysql] #prompt="(u:HOSTNAME:)[d]> " prompt="u@h R:m:s [d]> " no-auto-rehash [mysqld] user = mysql port = 3306 socket = /tmp/mysql.sock basedir = /usr datadir = /u02/mysql/data character-set-server = utf8mb4 skip_name_resolve = 1 open_files_limit = 3072 back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 512 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 51 query_cache_size = 22M #default_table_type = InnoDB tmp_table_size = 96M max_heap_table_size = 96M slow_query_log = 1 slow_query_log_file = /u02/mysql/log/slow.log log-error = /u02/mysql/log/error.log long_query_time = 3 server-id = 77 log-bin = /u02/mysql/log_bin/log_bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 1024M expire_logs_days = 1 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = row relay_log_recovery = 1 key_buffer_size = 15M 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 = 10G myisam_repair_threads = 1 myisam_recover transaction_isolation = REPEATABLE-READ innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 502M innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1024M:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_locks_unsafe_for_binlog = 0 [mysqldump] quick max_allowed_packet = 32M 主库: cd /usr/local/mysql/scripts [root@ scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/u02/mysql/data/ cp ./support-files/mysql.server /etc/init.d/mysqld 主库修改密码 导入所需求库 mysql -uroot -p test <test.sql 利用percona-xtrabackup备份库 主从安装percona-xtrabackup wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.10/binary/redhat/6/x86_64/percona-xtrabackup-2.2.10-1.el6.x86_64.rpm innobackupex --socket=/data/mysql/mysql.sock --parallel=8 --user=root --password=XMSSSS /data/backup/hotbackup scp -r /data/backup/hotbackup/* root@备库:/u02/mysql/hotbackup 主库建帐户 GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从库ip' IDENTIFIED BY '密码'; 从库恢复 cd /u02/mysql/hotbackup 注意在从库恢复之前建好my.cnf,如果主库打开忽略大小写,从库也要一样,不然会报错 innobackupex --user=root --password=密码 --defaults-file=/etc/my.cnf --apply-log /u02/mysql/hotbackup/2015-10-28_17-30-50/ innobackupex --copy-back /u02/mysql/hotbackup/2015-10-28_17-30-50/ chown -R mysql:mysql /u02/mysql service mysql start cat xtrabackup_binlog_info log_bin.000006 928891450 记住此logbin和pos CHANGE MASTER TO MASTER_HOST='主IP', MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_LOG_FILE='log_bin.000006', MASTER_LOG_POS=928891450; start slave; show slave status G 感谢各位的阅读,以上就是“Mysql利用percona-xtrabackup在线配置主从的方法”的内容了,经过本文的学习后,相信大家对Mysql利用percona-xtrabackup在线配置主从的方法这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。 (编辑:站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |