搭建华为云主库，自建数据库从库复制映射

源库(华为云)：   rds-gray-薪商城      langraymall.internal.cn-south-1.mysql.rds.myhuaweicloud.com

目标库（自建数据库）：  gray-xsc-mysql-slave  10.22.50.199L3307


步骤：
1、主库控制台设置RDS for MySQL本地Binlog日志清理(需要设置大于0的值，默认是0，表示Binlog备份成功后本地日志会被删除。)
2、主库mysqldump  --single-transaction --master-data=2 --flush-logs -n --databases 多个库
3、修改导出文件的use database为目标database
4、从库reset master
5、恢复sql文件到目标库
6、reset slave all
7、change master
8、start slave



注意：主库需要控制台设置RDS for MySQL本地Binlog日志清理，默认保留时间是0，需根据预估备份恢复时长，设置Binlog本地保留时长，正常保留1天，否则日志会自动删除，导致主从配置失败



mysql -usystem -p"Shang%#&like@2020" 

skip-grant-tables

mysqld --defaults-file=/etc/my.cnf &


mysql -uarch_wt -p"2uID@8SNF2OhGecg"


cat >   /data/backup/mysqldump_xqc_collect.sh << EOF
#!/bin/bash
source /etc/bashrc
source /etc/profile       
mysqldump  -uarch_wt -p"2uID@8SNF2OhGecg" --set-gtid-purged=OFF    xqc_collect          > /data/backup/20231120-xqc_collect        
EOF




chmod 775 /data/backup/mysqldump_xqc_collect.sh

nohup  /data/backup/mysqldump_xqc_collect.sh &



/opt/mysql/mysql-5.6.43-linux-glibc2.12-x86_64/scripts/mysql_install_db --basedir=/opt/mysql/mysql --datadir=/data/mysql_3306/data  --user=mysql   



mysqld --defaults-file=/etc/my.cnf &


启动MySQL
cp /opt/mysql/mysql-5.6.43-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld

/etc/init.d/mysqld start

MySQL 开机自启动
chkconfig --add mysqld


修改密码

use mysql;
update mysql.user set authentication_string=password('root.COM2020') where user='root' ;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root.COM2020' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root.COM2020' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'arch_wt'@'%' IDENTIFIED BY '2uID@8SNF2OhGecg' WITH GRANT OPTION;
flush privileges;


 
GRANT ALL ON xqc_collect.* TO 'dbreader'@'10.22.%.%' identified   by  'test#9918qax';
GRANT select ON *.* TO  'dbreader'@'10.22.%.%';



mysql -h10.22.50.199 -P3306 -uarch_wt -p"2uID@8SNF2OhGecg"

 
mysql -h10.22.50.199 -P3306 -udbreader -p"test#9918qax" xqc_collect -A




replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = apolloconfigdb
replicate-ignore-db = apolloportaldb
replicate-ignore-db = shanglike_mall
replicate-ignore-db = shanglike_recharge
replicate-ignore-db = xqc_gifts
replicate-ignore-db = xqc_layout
replicate-ignore-db = xqc_live
replicate-ignore-db = xqc_mall_new
replicate-ignore-db = xqc_mall_v2
replicate-ignore-db = xqc_market
replicate-ignore-db = xqc_omall
replicate-ignore-db = xqc_omall_beta

replicate-rewrite-db=xqc_admin->xqc_collect
replicate-rewrite-db=xqc_locallife->xqc_collect
replicate-rewrite-db=xqc_uaac->xqc_collect
replicate-rewrite-db=xqc_market->xqc_collect
replicate-rewrite-db=xqc_trade->xqc_collect
replicate-rewrite-db=xqc_commission->xqc_collect
replicate-rewrite-db=xqc_journey->xqc_collect
replicate-rewrite-db=xqc_mall->xqc_collect
replicate-rewrite-db=xqc_proc->xqc_collect
replicate-rewrite-db=xqc_bill->xqc_collect
replicate-rewrite-db=xqc_supplier->xqc_collect
replicate-rewrite-db=xqc_interests->xqc_collect


 
mysqldump  -hlangraymall.internal.cn-south-1.mysql.rds.myhuaweicloud.com  -uarch_wt -P6033 -p"2uID@8SNF2OhGecg"  --single-transaction --master-data=2 --flush-logs --no-create-db --databases \
xqc_admin       \
xqc_locallife   \
xqc_uaac        \
xqc_market      \
xqc_trade       \
xqc_commission  \
xqc_journey     \
xqc_mall        \
xqc_proc        \
xqc_bill        \
xqc_supplier    \
xqc_interests   \
> 20231129-xqc_collect

 

 
sed -i 's/`xqc_admin`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_locallife`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_uaac`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_market`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_trade`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_commission`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_journey`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_mall`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_proc`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_bill`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_supplier`/`xqc_collect`/g'   20231129-xqc_collect
sed -i 's/`xqc_interests`/`xqc_collect`/g'   20231129-xqc_collect


 

导入之前先执行 reset master;


mysql -h10.22.50.199 -P3307 -uarch_wt -p"2uID@8SNF2OhGecg"   xqc_collect  < 20231129-xqc_collect
 

 

mysql -h10.22.50.199 -P3307 -uarch_wt -p"2uID@8SNF2OhGecg"   xqc_collect   -A

reset slave all;


 
change master to master_host='langraymall.internal.cn-south-1.mysql.rds.myhuaweicloud.com',master_user='arch_wt',master_password='2uID@8SNF2OhGecg',master_port=6033,MASTER_AUTO_POSITION = 1;

 
start slave;