mysql从库重建

最新推荐文章于 2024-09-14 17:07:42 发布

weixin_40410849

最新推荐文章于 2024-09-14 17:07:42 发布

阅读量2.6k

点赞数

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/weixin_40410849/article/details/78342496

版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

方法一：通过mysqldump方法（只适合小于5G的库）
-----cat mysqldump_3901.sh
$MYSQL_HOME/mysql --login-path=root --socket=/test/my3901/var/mysql.sock -e 'stop slave;'
/test/mysql/base/bin/mysql --login-path=root --socket=/test/my3901/var/mysql.sock -e 'reset master;'
mysqldump -u dbmgr -p'*********' --protocol=TCP --port=3308 --host=10.33.28.36 --default-character-set=UTF8MB4 --single-transaction --events --triggers --routines --all-databases | /test/mysql/base/bin/mysql --login-path=root --socket=/test/my3901/var/mysql.sock

/test/mysql/base/bin/mysql --login-path=root --socket=/test/my3901/var/mysql.sock -e "change master to master_host='10.33.28.36',master_user='dbsync',master_password='***********',master_port=3308,master_auto_position = 1;"
/test/mysql/base/bin/mysql --login-path=root --socket=/test/my3901/var/mysql.sock -e 'start slave;'
/test/mysql/base/bin/mysql --login-path=root --socket=/test/my3901/var/mysql.sock -e 'flush privileges;'

方法二：mysqlbackup方式
步骤
1 主库做全备份
备份会锁myisam表，如master是生产库，需要注意，可留意业务进程是否有活动的。

/test/mysql/base/bin/mysqlbackup --defaults-file=/test/my3352/my.cnf --socket=/test/my3352/var/mysql.sock --user=root --password --with-timestamp --backup-dir=/test/my3352/backup backup-and-apply-log

2 备份文件拷到从库主机，做恢复
保证从库的data和log目录为空，用从库的my.cnf进行恢复
恢复时可能也会将主库备份的binlog和my.cnf直接恢复到data目录下，可以清理掉

mv data data_bak
mv log log_bak
mkdir data
mkdir log

停从库，清空目录

mysqlbackup --defaults-file=/test/my3309/my.cnf --backup-dir=/test/myback/BOT/temp copy-back

$ ps -ef | grep my3330
mysql 12116 16335 0 May03 ? 00:00:00 /bin/sh /test/mysql/5.6.22/bin/mysqld_safe --defaults-file=/test/my3330/my.cnf
mysql 13096 12116 0 May03 ? 00:49:58 /test/mysql/5.6.22/bin/mysqld --defaults-file=/test/my3330/my.cnf --basedir=/test/mysql/5.6.22
--datadir=/test/my3330/data --plugin-dir=/test/mysql/5.6.22/lib/plugin --log-error=/test/my3330/log/error.log
--pid-file=/test/my3330/var/mysql.pid --socket=/test/my3330/var/mysql.sock --port=3330

3 启动从库
Cd $MYSQL_HOME
mysqld_safe --defaults-file=/test/mydat/$PORT/my.cnf &

mysql -uroot --socket=/test/my3330/var/mysql.sock

4 执行备份文件meta目录下backup_gtid_executed.sql文件的SET @@GLOBAL.GTID_PURGED语句(可source backup_gtid_executed.sql)

SET @@GLOBAL.GTID_PURGED='0b167c90-e335-11e4-91c5-842b2b516d99:1-6804,498d32e4-30d5-11e3-814b-842b2b516d99:1-134:136-321:323-496:498-668:670-840:842-1011:1013-1181:1183-1353:1355-1537:1539-1708:1710-1881:1883-2052:2054-2223:2225-2394:2396-2564:2566-2748:2750-2919:2921-3090:3092-3361:3363-3374:3376-3387:3389-3400:3402-3414:3416-3427:3429-3440:3442-3453:3455-3466:3468-3479:3481-3492:3494-3506:3508-3519:3521-3532:3534-3545:3547-3558:3560-3571:3573-3584:3586-3598:3600-3611:3613-3624:3626-3637:3639-3650:3652-3663:3665-3678:3680-3692:3694-3707:3709-3720:3722-3733:3735-3746:3748-3759:3761-3772:3774-3786:3788-3799:3801-3812:3814-3825:3827-3838:3840-3851:3853-3864:3866-3878:3880-3891:3893-3904:3906-3917:3919-3930:3932-3943:3945-3956:3958-3970:3972-3983:3985-3996:3998-4009:4011-4022:4024-4035:4037-4048:4050-4062:4064-4075:4077-4088:4090-4103:4105-4116:4118-4129:4131-4142:4144-4156:4158-4169:4171-4183:4185-4196:4198-4209:4211-4222:4224-4235:4237-4249:4251-4262:4264-4275:4277-4288:4290-4301:4303-4314:4316-4327:4329-4341:4343-4354:4356-4367:4369-4380:4382-4393:4395-4406:4408-4419:4421-4433:4435-4446:4448-4459:4461-4472:4474-4486:4488-4499:4501-4512:4514-4526:4528-4539:4541-4552:4554-4565:4567-4578:4580-4591:4593-4606:4608-4620:4622-4633:4635-4642:4645-4648:4650-4661:4663-4674:4676-4687:4689-4700:4702-4714:4716-4727:4729-4740:4742-4753:4755-4766:4768-4779:4781-4792:4794-4806:4808-4819:4821-4832:4834-4845:4847-4858:4860-4872:4874-4888:4890-4901:4903-4914:4916-5258:5260-5271:5273-5284:5286-5297:5299-5310:5312-5323:5325-5336:5338-5350:5352-5363:5365-5376:5378-5389:5391-5402:5404-5415:5417-5428:5430-5442:5444-5455:5457-5468:5470-5481:5483-5503:5505-5516:5518-5529:5531-5543:5545-5556:5558-5569:5571-5582:5584-5595:5597-5608:5610-5621:5623-5635:5637-5648:5650-5661:5663-5674:5676-5687:5689-5700:5702-5713:5715-5727:5729-5740:5742-5753:5755-5766:5768-5779:5781-5792:5794-5805:5807-5819:5821-5832:5834-5845:5847-5858:5860-5871:5873-5884:5886-5897:5899-5911:5913-5924:5926-5937:5939-5950:5952-5963:5965-5976:5978-5989:5991-6003:6005-6016:6018-6029:6031-6042:6044-6055:6057-6068:6070-6081:6083-6095:6097-6108:6110-6121:6123-6134:6136-6147:6149-6160:6162-6173:6175-6187:6189-6200:6202-6213:6215-6226:6228-6239:6241-6252:6254-6265:6267-6278:6280-6291:6293-6304:6306-6317:6319-6330:6332-6343:6345-6356:6358-6369:6371-6382:6384-6395:6397-6408:6410-6421:6423-6434:6436-6452:6454-6465:6467-6478:6480-6491:6493-6504:6506-6517:6519-6535:6537-6548:6550-6561:6563-6574:6576-6587:6589-6600:6602-6618:6620-6631:6633-6644:6646-6657:6659-6670:6672-6683:6685-6701:6703-6714:6716-6727:6729-6740:6742-6753:6755-6766:6768-6784:6786-6797:6799-6810:6812-6823:6825-6836:6838-6849:6851-6867:6869-6880:6882-6893:6895-6906:6908-6919:6921-6932:6934-6950:6952-6963:6965-6976:6978-6989:6991-7002:7004-7013:7015-7019,af9903be-8463-11e5-acd0-00e0ed2fa3a2:1-260822770:413493139,b35ac611-d4af-11e4-b315-a0369f1fe9b7:1-4:6-17:19-30:32-43:45-56:58-69:71-82:84-95:97-109:111-122:124-135:137-148:150-161:163-174:176-187:189-201:203-214:216-223,b908e825-30d3-11e3-8141-782bcb0fa8d7:1-792796459:792796461-875476880,bbdbe993-8776-11e5-80dd-00e0ed42a2a2:1-282017894';

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> start slave ;
Query OK, 0 rows affected (0.04 sec)

mysql> show slave status\G

5 按标准change master to

mysql> change master to master_host='10.33.45.152', master_port=3330,master_user='dbsync', master_password='********', master_auto_position=1;

6 start slave。
可能会报ERROR 1872，可以reset slave后，再重新change master.
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

$ slave
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.33.45.152
Master_User: dbsync
Master_Port: 3330
Connect_Retry: 60
Master_Log_File: mysql-bin.001565
Read_Master_Log_Pos: 68891704
Relay_Log_File: relay-log.003252
Relay_Log_Pos: 1587
Relay_Master_Log_File: mysql-bin.000986
Slave_IO_Running: No
Slave_SQL_Running: No