[100,/var/lib/mysql/new-master-slow.log]" vim /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh...grep 120 /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh yum install libev rpm -e xtrabackup...innobackupex --version time nohup /usr/bin/innobackupex --apply-log /data/nfs/test_full_backup/2015-12...-09_00-53-03/ time nohup /usr/bin/innobackupex --copy-back /data/nfs/test_full_backup/2015-12-09_00-53...chown -R mysql.mysql /var/lib/mysql/ 原文地址
前言 有了keepalived和mysql upgrade的技术作基础,可以结合两者完成无逢升级 下面分享一下我的 Best Practice Of Mysql Migration ---- 概要 --...-- 准备工作 下面步骤最好作为准备工作,提前完成,这样可以更有效进行切换,和减少窗口期 挂载NFS 安装软件包的收集(mysql,xtrabackup,keepalived) 安装keepalived
说明: 迁移数据有很多工具的, 后续可能会分享其它的. Kettle最早是一个开源的ETL工具, 2006年被Pentaho收购了,....源端:oracle 11g/12c/19c都可以 目标端: mysql/mariadb 反之也可以. 1....本次实验环境 操作系统: oel7.8 源端数据库: oracle 12.2 目标端数据库 : mariadb 5.5 迁移工具: kettle版本: pdi-ce-7.1.0.0-12 这个工具是图形化的...使用kettle迁移数据 4.1 创建转换 文件 --> 新建 --> 转换 2021-02-18_220515.png 4.2 创建 DB连接 我得连上数据库才能迁数据吧.......比较麻烦, 所以我耍个小聪明,把mariadb当成mysql来用. mysql的驱动你应该有把, 没得的话安装 mysql-connector-java 就有了/usr/share/java/mysql-connector-java.jar
[root@slave01 data]# cd /var/lib/mysql/ [root@slave01 mysql]# ls livedb slave01-relay-bin.000308....000309 ibdata1 wavedb mysql-bin.000155 mysql-bin.000160 functiondb mysqltest_his slave01...-relay-bin.index ib_logfile0 master.info mysql-bin.000156 mysql-bin.index test mysqltestt_db...ib_logfile2 mysql mysql-bin.000158 keydb [root@slave01 mysql]# rm -rf * [root@slave01 mysql...16G 3.7G 12G 25% / tmpfs 16G 0 16G 0% /dev/shm /dev/sda1 194M
myisam_recover_options [testuser@slave01 etc]$ innodb_additional_mem_pool_size 也已经被弃用了,如果有要注释掉 ---- 将zabbit加入mysql...组以方便监控 [root@new-master mysql]# vim /etc/group [root@new-master mysql]# id zabbix uid=496(zabbix) gid...=493(zabbix) groups=493(zabbix),492(mysql) [root@new-master mysql]# /etc/init.d/zabbix-agent restart...]# ---------- [root@zabbix-server ~]# zabbix_get -s new-master -p 10050 -k "mysql.slowlog[100,/var/lib.../mysql/new-master-slow.log]" 2.98465 [root@zabbix-server ~]#
修改zabbix统计数据过期时间 [root@new-master mysql]# vim /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh...[root@new-master mysql]# grep 120 /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh if...[ `expr $TIMENOW - $TIMEFLM` -gt 120 ]; then [root@new-master mysql]# 修改之前是300,也就是5分钟,这个监控粒度太粗,所以改为...-4.013-3.el6.x86_64 has missing requires of libmysqlclient.so.16()(64bit) perl-DBD-MySQL-4.013-3.el6....x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit) php-mysql-5.3.3-40.el6_
新版master上安装并启动keepalived 安装并启动keepalived [root@new-master ~]# yum -y install ke...
备份完成 xtrabackup: Creating suspend file '/data/nfs/test_full_backup/2015-12-09_00-53-03/xtrabackup_log_copied...innobackupex: All tables unlocked innobackupex: Backup created in directory '/data/nfs/test_full_backup/2015-12...-09_00-53-03' innobackupex: MySQL binlog position: filename 'mysql-bin.000004', position 8299670 151209...innobackupex [root@slave01 nfs]# time nohup /usr/bin/innobackupex --apply-log /data/nfs/test_full_backup/2015-12...恢复数据 [root@slave01 data]# time nohup /usr/bin/innobackupex --copy-back /data/nfs/test_full_backup/2015-12
Done time nohup /usr/bin/innobackupex --copy-back /data/nfs/test_full_backup/2015-12...]# chown -R mysql.mysql /var/lib/mysql/ [root@slave02 mysql]# ll total 5916780 drwx------ 2 mysql mysql...---- 2 mysql mysql 36864 Dec 9 03:57 mysqltestt_db drwx------ 2 mysql mysql 4096 Dec 9 03...4096 Dec 9 02:45 mysql drwx------ 2 mysql mysql 4096 Dec 9 02:49 testdb drwx------ 2 mysql mysql...mysql mysql 20480 Dec 9 03:08 /data/mysql/ [root@slave02 mysql]#
恢复数据 [root@slave01 data]# time nohup /usr/bin/innobackupex --copy-back /data/nfs/test_full_backup/2015-12...要确保mysql 数据库的 datadir是清空的,否则会报错 [root@slave02 data]# cat restore.log nohup: ignoring input Warning:...) Original data directory /var/lib/mysql is not empty!...) Original data directory /var/lib/mysql is not empty!...开头的文件 如: .bash_history .lesshst .mysql_history .viminfo 要指明删,如 rm -rf .bash_history .lesshst .mysql_history
要确保mysql 数据库的 datadir是清空的,否则会报错 [root@slave02 data]# cat restore.log nohup: ignoring input Warning:...) Original data directory /var/lib/mysql is not empty!...) Original data directory /var/lib/mysql is not empty!...开头的文件 如: .bash_history .lesshst .mysql_history .viminfo 要指明删,如 rm -rf .bash_history .lesshst .mysql_history...Done time nohup /usr/bin/innobackupex --copy-back /data/nfs/test_full_backup/2015-12
启动mysql并且开启同步 [root@slave02 mysql]# mysql -u root -p Enter password: Welcome to the MySQL monitor....mysql> show slave status\G Empty set (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.66.100'...innobackupex --version time nohup /usr/bin/innobackupex --apply-log /data/nfs/test_full_backup/2015-12...-09_00-53-03/ time nohup /usr/bin/innobackupex --copy-back /data/nfs/test_full_backup/2015-12-09_00-53...chown -R mysql.mysql /var/lib/mysql/ 原文地址
启动mysql并且开启同步 [root@slave02 mysql]# mysql -u root -p Enter password: Welcome to the MySQL monitor....Your MySQL connection id is 1 Server version: 5.6.27-75.0-log Percona Server (GPL), Release 75.0, Revision...mysql> show slave status\G Empty set (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.66.100'...=8299670; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> show slave status\G **************...hostname slave02 mysql>
更新slave mysql版本 [root@slave02 src]# rpm -e Percona-Server-client-51-5.1.73-rel14.11.603.rhel6.x86_64...rhel6.x86_64 Percona-Server-shared-51-5.1.73-rel14.11.603.rhel6.x86_64 error: Failed dependencies: mysql...-4.013-3.el6.x86_64 libmysqlclient.so.16()(64bit) is needed by (installed) php-mysql-5.3.3-40.el6_6....-4.013-3.el6.x86_64 libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) php-mysql...SONAME 'libfnv1a_udf.so'" mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" mysql
关闭原集群mha 由于集群软件在侦测到主服务器失效后会干预相关资源,造成备机身份切换和IP飘移,为了避免这种影响,要关掉集群 [mysql@slave02 bin]$ masterha_check_status...[mysql@slave02 bin]$ ps faux | grep manager mysql 27192 0.0 0.0 103244 864 pts/2 S+ 00:23...Ss Aug12 12:49 /usr/sbin/keepalived -D root 17799 0.0 0.0 112300 1564 ?...S Aug12 13:18 \_ /usr/sbin/keepalived -D root 17800 0.0 0.0 112300 1036 ?...S Aug12 88:32 \_ /usr/sbin/keepalived -D [root@slave01 tmp]# ip a 1: lo:
备份完成 xtrabackup: Creating suspend file '/data/nfs/test_full_backup/2015-12-09_00-53-03/xtrabackup_log_copied...innobackupex: All tables unlocked innobackupex: Backup created in directory '/data/nfs/test_full_backup/2015-12...-09_00-53-03' innobackupex: MySQL binlog position: filename 'mysql-bin.000004', position 8299670 151209...innobackupex [root@slave01 nfs]# time nohup /usr/bin/innobackupex --apply-log /data/nfs/test_full_backup/2015-12
领取专属 10元无门槛券
手把手带您无忧上云