歌声与微笑

要把阿里云rds mysql数据库搬回ECS服务器，搜索官方帮助，可选两种方法
一是逻辑备份数据库，下载到本地后用mysql命令导入或使用source命令导入。
二是物理备份数据库，下载后解压恢复到本地服务器（但此方法只能恢复到linux服务器，不支持win系统）。
官方的教程不详细，故记录下此次操作。

选择物理备份的数据库进行恢复，过程如下：
1、从阿里云下载rds到外网是按流量收费的，故使用内网地址下载到ECS服务器较好。因客户的机子是win系统，所以先在rds上点击下载，复制内网地址，然后在win机子上打开浏览器进行下载，下载完成后，得到了hins24216484_data_20230216044728_qp.xb的备份文件。因此ECS机子性能一般，所以再次从ECS上下载文件到本地。
2、在本地安装VMWARE，再安装centos8，完成后安装了BT（在此选择相同版本的mysql5.6进行安装，否则要使用mysql源码编译安装）。
3、对于MySQL 5.7、5.6或5.5实例：下载及安装Percona XtraBackup 2.4。
yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum -y  install percona-xtrabackup-24  
安装qpress解压缩软件
yum -y  install qpress
安装完成后测试下
innobackupex -version
能正常返还即为成功
4、创建存放mysql的目录
mkdir -p /home/mysql/data
5、上传_data_20230216044728_qp.xb至/home目录并更名为h_qp.xb
6、解包
cat h_qp.xb | xbstream -x -v -C /home/mysql/data
7、解压
innobackupex --decompress --remove-original /home/mysql/data
8、执行备份还原命令
innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
9、编辑backup-my.cnf
vi /home/mysql/data/backup-my.cnf
在[mysqld]段内增加：lower_case_table_names=1 并屏蔽其他行。再增加：skip-grant-tables
10、修改目录所属
chown -R mysql:mysql /home/mysql/data
11、停止原已启动的bt的mysql
service mysqld stop
12、启动mysql
/www/server/mysql/bin/mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data/ & 回车
13、登录mysql
/www/server/mysql/bin/mysql -uroot -p
无密码回车后登录，此时可以查看下数据库。
show databases;
14、修改密码
use mysql;
update user set password=PASSWORD("123654") where user='root';
FLUSH PRIVILEGES;
quit;
退出后杀掉启动的mysql（killall -9 mysqld），删掉/home/mysql/data/backup-my.cnf的skip-grant-tables，然后重新启动下。
/www/server/mysql/bin/mysql -uroot -p
这时会发现需要输入密码123654后登录。
15、导出数据库
a、停止数据库进程后拷贝出整个/home/mysql/data目录，放到win主机上的相应目录里（需先在win主机上安装好mysql5.6）
b、也可以用mysqldump方式导出数据库，再到win主机上导入数据库（此方式适用于主机上原本就运行了其他innodb类型的mysql程序，无法直接使用，只能新增进去）。
导出
/www/server/mysql/bin/mysqldump -u root -p -c --default-character-set=utf8 h > h.sql
在win主机上导入
mysql -uroot -p --default-character-set=utf8 h < d:\h.sql

此前，我是先尝试过使用逻辑备份数据库下载到本地来恢复，通过命令
mysql -f -uroot -p h.sql > test
或登录mysql后使用 source h.sql 来恢复，但在恢复一会儿后就显示连接不上数据库了，尝试调大连接超时参数等无效，遂放弃。然后再尝试物理备份数据库方式到本地恢复。本次要搬的数据库在36G，备份下来的sql文件也有12G多。想要打开修改都难。

参考官方教程
https://help.aliyun.com/document_detail/41817.html#section-ooe-3fz-r97

最后：mysql到本地使用最好还是改回MYISAM数据引擎方式，Innodb方式管理不是很方便，所以，在本地恢复数据库成功后，使用phpmyadmin 来运行转换语句，如：ALTER TABLE   pre_common_admincp_cmenu  ENGINE = MYISAM; 数据表的列表可以使用ll列出来取出，等全部转换完成，就可以把单独把数据库目录上传到数据库目录 data下使用了。

准备相应的mysql程序，软件安装在/home/web目录，因不用再花大量时间编译，安装很快，较省时。

安装脚本如下：

cur_dir=$(pwd)

# 配置myisam或innodb方式
read -p "do you want to install the InnoDB Storage Engine? (y/n)" mysqlengineer
if [ "$mysqlengineer" = 'y' ]; then
  echo "mysqlengineer is innoDB"
elif
[ "$mysqlengineer" = 'n' ]; then
  echo "mysqlengineer is MYSIAM"
else
   mysqlengineer="y" && echo "mysqlengineer is innoDB"
fi

#确认后暂停

get_char()
{
SAVEDSTTY=`stty -g`
stty -echo
stty cbreak
dd if=/dev/tty bs=1 count=1 2> /dev/null
stty -raw
stty echo
stty $SAVEDSTTY
}
echo ""
echo "Press any key to start..."
char=`get_char`

#开始安装
mkdir -p /home/web

if [ `uname -m` = "x86_64" ] ; then
tar zxvf mysql-5.5.44-linux2.6-x86_64.tar.gz
mv mysql-5.5.44-linux2.6-x86_64 /home/web/mysql
else
tar zxvf mysql-5.5.44-linux2.6-i686.tar.gz
mv mysql-5.5.44-linux2.6-i686 /home/web/mysql
fi

cd /home/web/mysql/
groupadd mysql
useradd -s /sbin/nologin -M -g mysql mysql -d /dev/null
rm -f /etc/my.cnf
cp /home/web/mysql/support-files/my-medium.cnf /etc/my.cnf
sed -i 's/log-bin=mysql-bin/#log-bin=mysql-bin/g' /etc/my.cnf
sed -i 's/binlog_format=mixed/#binlog_format=mixed/g' /etc/my.cnf
sed -i 's/skip-federated/#skip-federated/g' /etc/my.cnf
sed -i 's/skip-locking/skip-locking\nmax_connections = 1000\nwait_timeout = 5/g' /etc/my.cnf
sed -i 's:/usr/local/:/home/web/:g' /etc/my.cnf
if [ $mysqlengineer = "y" ]; then
sed -i 's:#innodb:innodb:g' /etc/my.cnf
else
sed '/skip-external-locking/i\default-storage-engine=MyISAM\nloose-skip-innodb' -i /etc/my.cnf
fi

/home/web/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/home/web/mysql --datadir=/home/web/mysql/data --user=mysql
chown -R mysql /home/web/mysql/data
chgrp -R mysql /home/web/mysql/.
cp /home/web/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
sed -i 's:/usr/local:/home/web:g' /etc/init.d/mysqld
service mysqld start
/home/web/mysql/bin/mysqladmin -u root password no123654

MySQL主从数据库配置注意问题:

   一般使用MySQL的时候，如果数据量不大，我们都使用一台MySQL服务器，备份的时候使用mysqldump工具就可以了，但是随着业务不断发展，问题出现了：  
       首先：数据量往往直线上升，单独一台数据库服务器开始出现性能的瓶颈，数据访问越来越慢。
       其次：备份也变得困难了，因为mysqldump是导出一份文本文件，而数据量特别大的时候，这样的备份往往需要很长时间，可能有人会说，我们可以直接通过拷贝数据文件来备份数据库，这样很方便，快捷，不错，这样是比mysqldump方便快捷，但是，直接拷贝数据文件备份的方式要求我们必须先关闭mysql服务，然后再拷贝数据文件，否则，你拷贝的文件很可能是坏的。而实际运行的mysql服务往往要求在任何时候都不可以停止服务，所以这样的备份方式在此情况下不可行。
       如果你遇到了类似上面的问题，你就可以使用建立MySQL主从服务器的方式来解决，下面先来看看主从服务器的设置：
       前提：MySQL主从服务器最好使用相同的软件版本，以避免不不可预期的故障。
首先设置MySQL主服务器：
在主服务器上为从服务器建立一个用户任意主机用户：
grant replication slave on *.* to '用户名'@'主机' identified by '密码';
也可以使用phpmyadmin来管理建立用户和数据库
编辑主服务器的配置文件：/etc/my.cnf的mysqld处

server-id = 1
log-bin
binlog-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可
编辑从服务器的配置文件：/etc/my.cnf
server-id=2
master-host=主机
master-user=用户名
master-password=密码
master-port=端口
replicate-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
记得先手动同步一下主从服务器中要备份的数据库，然后重启主，从服务器。
要验证主从设置是否已经成功，可以登录从服务器输入如下命令：
mysql> show slave status\G
会得到类似下面的列表：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
       如果这两个选项不全是Yes，那就说明你前面某个步骤配置错了。
        如果你的设置是正确的，尝试在主服务器上插入若干条记录，然后你再转到从服务器，会发现相应的新记录已经自动同步过来了。
       如果你的主从服务器已经配置好了，那么你在应用程序中，只要保证所有的insert/delete/update操作是在主服务器上进行的，那么相应的数据变化会自动同步到从服务器上，这样，我们就可以把select操作分担到多台从数据库上，从而降低服务器的载荷。
       如果你想使用复制数据文件的方式来备份数据库，只要在从服务器上的mysql命令行先键入slave stop;然后复制数据库文件，复制好了，再 在mysql命令行键入slave start;启动从服务器，这样就即备份了数据有保证了数据完整性，而且整个过程中主服务器的mysql无需停止。
-----------------------------------------------------------------------------------
提示：如果修改了主服务器的配置，记得删除从服务器上的master.info文件。否则从服务器使用的还是老配置，可能会导致错误。
-----------------------------------------------------------------------------------
注意：关于要复制多个数据库时，binlog-do-db和replicate-do-db选项的设置，网上很多人说是用半角逗号分隔，经过测试，这样的说法是错误的，MySQL官方文档也明确指出，如果要备份多个数据库，只要重复设置相应选项就可以了。
比如：
binlog-do-db=a
binlog-do-db=b
replicate-do-db=a
replicate-do-db=b
-----------------------------------------------------------------------------------
补充：从服务器上my.cnf中的master-*的设置仅在第一次生效，后保存在master.info文件里。
-----------------------------------------------------------------------------------
在从服务器上使用show slave status
Slave_IO_Running,为No,则说明IO_THREAD没有启动，请执行slave start [IO_THREAD]
Slave_SQL_Running为No则复制出错,查看Last_error字段排除错误后执行slave start [SQL_THREAD]
查看Slave_IO_State字段
空 //复制没有启动
Connecting to master//没有连接上master
Waiting for master to send event//已经连上
-----------------------------------------------------------------------------------
可以使用LOAD DATA FROM MASTER语句来建立slave。但有约束条件：
数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备RELOAD和SUPER权限。
在master端执行RESET MASTER清除已有的日志变更，
此时slave端会因为找不到master日志无法启动IO_THREAD，请清空data目录下
relay-log.info,hosname-relay-bin*等文件重新启动mysql
中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的--
relay-log和--relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用
--relay-log-info-file启动选项修改文件名。
双机互备则是两个mysql同时配置为master及slave
-----------------------------------------------------------------------------------
主服务器上的相关命令：                    
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}
-----------------------------------------------------------------------------------
从服务器上的相关命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志
-----------------------------------------------------------------------------------
--read-only
该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新。可以确保从服务器不接受来自客户的更新。

在配置mysql主从同步的时候遇到了查看从服务器状态显示为:
Slave_SQL_Running:No

搜索通过以下第一种方法解决.主服务器已有数据,而从服务器因为当时配置的数据库版本不一致导致没法同步,后安装同一数据库版本后,运行状态为Slave_SQL_Running:No

以下为转贴:

故障状态:
进入slave服务器，运行：

mysql> show slave status\G

         .......
             Relay_Log_File: localhost-relay-bin.000535
              Relay_Log_Pos: 21795072
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
      ......

解决办法一、

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;



解决办法二、

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)



然后到slave服务器上执行手动同步：

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)



mysql> show slave status\G
*************************** 1. row ***************************
........
            Master_Log_File: localhost-bin.000094
        Read_Master_Log_Pos: 33768775
             Relay_Log_File: localhost-relay-bin.000537
              Relay_Log_Pos: 1094034
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

手动同步需要停止master的写操作！