Linux: RDS迁移自建操作步骤
0、修改服务器时区
timedatectl statusmysql_replication_hostgroups
timedatectl set-timezone "Asia/Shanghai"
1、部署MySQL
配置文件修改
cd /usr/local/src
sudo wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
sudo tar -zxvf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
sudo groupadd mysql
sudo useradd -r -g mysql -s /bin/false mysql
sudo mkdir -p /mysqldata/mysql /mysqldata/tmpdir /mysqldata/output /mysqllog/binlog /mysqllog/relaylog
sudo chown -R mysql:mysql /mysqldata/mysql /mysqldata/tmpdir /mysqldata/output /mysqllog/binlog /mysqllog/relaylog
sudo mv mysql-5.7.34-linux-glibc2.12-x86_64 ../mysql
sudo chown -R mysql:mysql /usr/local/mysql
sudo /usr/local/mysql/bin/mysqld –initialize –user=mysql
sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
sudo systemctl enable mysqld
sudo chkconfig mysqld on
sudo systemctl start mysqld
sudo systemctl status mysqld
sudo vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile
create database meta;
CREATE TABLE `cluster` (
`anchor` tinyint(4) NOT NULL,
`cluster_name` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '',
`cluster_domain` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '',
PRIMARY KEY (`anchor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into meta.cluster (anchor,cluster_name)values (1,'xxxxxxx') ;
创建MySQL用户
orchestrator拓扑用户
GRANT SELECT ON `meta`.`cluster` TO `orchestrator`@`172.21.39.6`;
GRANT SELECT ON `mysql`.`slave_master_info` TO `orchestrator`@`172.21.39.6`;
GRANT RELOAD, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON . TO `orchestrator`@`172.21.39.6`;
proxysql的监控用户
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON . TO `monitor`@`172.41.%`;
prometheus监控用户
GRANT SELECT, PROCESS, REPLICATION CLIENT ON . TO 'prometheus_exporter'@'127.0.0.1' ;
2、部署proxysql-cluster
sudo vim /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.1.x/centos/latest
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
sudo yum -y install proxysql
在两台proxysql服务器的proxysql配置文件添加proxysql集群服务器信息
vim /etc/proxysql.cnf
admin_variables=
{
admin_credentials="admin:admin;cluster_user:o"
cluster_username="cluster_user"
cluster_password="go"
}
proxysql_servers=
(
{
hostname="172.21.xx.xx"
port=6032
weight=1
comment="xxx-proxysql-node1"
},
{
hostname="172.21.xx.xx"
port=6032
weight=1
comment="xxx-proxysql-node2"
}
)
修改参数
update global_variables set variable_value=262144 where variable_name='mysql-stacksize';
update global_variables set variable_value=1073741824 where variable_name='mysql-max_allowed_packet';
update global_variables set variable_value='false' where variable_name='mysql-log_unhealthy_connections';
update global_variables set variable_value=200 where variable_name='admin-cluster_check_interval_ms';
update global_variables set variable_value=100 where variable_name='admin-cluster_check_status_frequency';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_query_rules_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_servers_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_users_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_proxysql_servers_save_to_disk';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_query_rules_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_servers_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_users_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_proxysql_servers_diffs_before_sync';
load admin variables to RUNTIME;
save admin variables to disk;
load mysql variables to RUNTIME;
save mysql variables to disk;
添加MySQL读写组
INSERT INTO mysql_replication_hostgroups VALUES (1,2,'read_only','w r group');
添加MySQL servers
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('172.41.52.74',1,3306,1);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('172.41.49.188',2,3306,1);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('172.41.56.75',2,3306,1);
load mysql servers to runtime;
save mysql servers to disk;
添加MySQL users
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema) VALUES ('alibet','MLYNQG6j4^ODHJnv',1,1,'information_schema');
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema) VALUES ('read_only','Agt@Fantasy#2021$readonly',1,2,'information_schema');
load mysql users to runtime;
save mysql users to memory;
save mysql users to disk;
3、添加到orchestrator
通过映射端口登陆orchestrator页面，填入ip获取集群
让只读库永远不升级为Master
继续往下添加/devops/dba_script/orchestrator_must_not.sh
例如：
##fantasy日常
orchestrator-client -c register-candidate -i ip-172-41-49-188.ap-south-1.compute.internal –promotion-rule prefer_not
4、 部署supervisor
tar -zxvf supervisor-4.2.2.tar.gz
sudo mv supervisor-4.2.2 /usr/local/supervisor
cd usr/local/supervisor
sudo python setup.py install
sudo -s
mkdir -p /etc/supervisor/conf.d
echo_supervisord_conf > /etc/supervisor/supervisord.conf
sudo mkdir -p /devops/nodexporter
sudo mkdir /devops/mysqld_exporter
sudo vim /etc/supervisor/supervisord.conf
[unix_http_server]
file=/var/run/supervisor.sock
;chmod=0700
;chown=nobody:nobody
[supervisord]
logfile=/var/log/supervisord.log
logfile_maxbytes=50MB
logfile_backups=2
loglevel=info
pidfile=/var/run/supervisord.pid
nodaemon=false
minfds=65535
minprocs=200
; the below section must remain in the config file for RPC
; (supervisorctl/web interface) to work, additional interfaces may be
; added by defining them in separate rpcinterface: sections
[rpcinterface:supervisor]
supervisor.rpcinterface_factory = supervisor.rpcinterface:make_main_rpcinterface
[supervisorctl]
serverurl=unix:///var/run/supervisor.sock
#[inet_http_server]
#port=10.211.55.11:9001
#username=user
#password=123
[include]
files = /etc/supervisor/conf.d/*.conf
sudo vim /etc/supervisor/conf.d/mysqld_exporter.conf
[program:fantasy_daily_mysqld_master]
command = /devops/mysqld_exporter/mysqld_exporter –collect.info_schema.query_response_time –collect.info_schema.tablestats –collect.engine_innodb_status –collect.info_schema.schemastats –collect.info_schema.processlist.processes_by_host
environment=DATA_SOURCE_NAME='prometheus_exporter:ascvf$$Wa#2Q4f@(127.0.0.1:3306)/'
user = root
autostart = true
autorestart = true
redirect_stderr=true
stdout_logfile_backups = 2
stdout_logfile = /devops/mysqld_exporter/exporter.log
sudo vim /etc/supervisor/conf.d/node_exporter.conf
[program:node_exporter]
command = /devops/nodexporter/node_exporter –collector.tcpstat –collector.filesystem.ignored-fs-types="^(autofs|cgroup2?|configfs|debugfs|devpts|tmpfs|fusectl|hugetlbfs|mqueue)$"
user = root
autostart = true
autorestart = true
redirect_stderr=true
stdout_logfile_backups = 2
stdout_logfile = /devops/nodexporter/node_exporter.log
sudo supervisord -c /etc/supervisor/supervisord.conf
sudo supervisorctl status all
[root@ip-172-21-39-187 nodexporter]# cat /usr/lib/systemd/system/supervisor.service
[Unit]
Description=Supervisor process control system for UNIX
Documentation=http://supervisord.org
After=network.target
[Service]
ExecStart=/usr/bin/supervisord -n -c /etc/supervisor/supervisord.conf
ExecStop=/usr/bin/supervisorctl $OPTIONS shutdown
ExecReload=/usr/bin/supervisorctl -c /etc/supervisor/supervisord.conf $OPTIONS reload
KillMode=process
Restart=on-failure
RestartSec=50s
[Install]
WantedBy=multi-user.target
5、部署mysqld_exporter
sudo mv mysqld_exporter devops/mysqld_exporter
6、部署node_exporter
sudo mv node_exporter devops/nodexporter
7、部署proxysql_exporter
sudo mkdir /devops/proxysqlexporter
sudo cp proxysql_exporter devops/proxysqlexporter
sudo vim /etc/supervisor/conf.d/proxysql_exporter.conf
[program:proxysql_exporter]
command = /devops/proxysqlexporter/proxysql_exporter
user = root
autostart = true
autorestart = true
redirect_stderr=true
stdout_logfile_backups = 2
stdout_logfile = /devops/proxysqlexporter/proxysql_exporter.log
sudo supervisorctl update
验证数据一致性
停服后 show full processlist;
show master status;观察binlog postition
select substring_index(host,':',1) IP,user,count() count from information_schema.PROCESSLIST GROUP BY substring_index(host,':',1),user ORDER BY count() DESC;
RDS
mysql -h agt-fantasy-prod-fantayslr-rds.co7izkdw11dl.ap-south-1.rds.amazonaws.com -u -p -Ne "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema','meta')" > database.txt
bash tablecount.sh > tablecountrds.txt
自建
mysql -h172.41.9.163 -u -p -Ne "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema','meta')" > database.txt
bash tablecount.sh > tablecount.txt
diff tablecountrds.txt tablecount.txt
回写验证
use testfordts
insert into student values (7,'zyx');
update student set name='super' where id=7;
delete from student where id=7;
tablecount.sh脚本
#!/bin/bash
for i in `cat ./database.txt`
do
##fantasylr
echo $i
mysql -hagt-fantasy-prod-fantayslr-rds.co7izkdw11dl.ap-south-1.rds.amazonaws.com -uxxxxxx -p'xxxxxx' -Ne"select count(*) from $i"
done
检查参数
select @@binlog_format,@@binlog_row_image,@@explicit_defaults_for_timestamp,@@innodb_file_format,@@innodb_large_prefix,@@lower_case_table_names,@@tx_isolation,@@time_zone,@@innodb_sync_array_size,@@max_connections,@@max_user_connections;
readonly服务器安装xtrabackup
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL perl-DBI perl-IO-Socket-SSL.noarch perl-TermReadKey perl-Digest-MD5 perl-ExtUtils-MakeMaker
percona-xtrabackup-2.4.16-Linux-x86_64.libgcrypt153.tar.gz
sudo wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
sudo yum -y install qpress-11-1.el7.x86_64.rpm
备份
sudo mkdir /mysqlback/bakscript /mysqlback/checkpoint /mysqlback/mysql_backup
sudo chown -R centos:centos mysqlback
30 03 * * * /bin/bash /mysqlback/bakscript/db_bak.sh 2>&1 >/dev/null
#!/bin/bash
USER='xxxxxxxx'
HOST='xxxxxxxxx'
PORT=3306
TODAY=`date +%F`
WEEK=`date +%w`
YESTERDAY=`date -d '-1 day' +%F`
sudo usr/local/src/percona-xtrabackup-2.4.16-Linux-x86_64/bin/innobackupex –defaults-file=/etc/my.cnf –no-timestamp –user=$USER –password='xxxxxxx' –host=$HOST –port=$PORT –no-version-check –ftwrl-wait-timeout=30 –slave-info –parallel=2 –compress –compress-threads=2 –tmpdir=/mysqlback –extra-lsndir=/mysqlback/checkpoint/$TODAY /mysqlback/mysql_backup –stream=xbstream 2>/mysqlback/db_bak.log |cat - > /mysqlback/mysql_backup/payment2full$TODAY.xbstream.gz
/usr/local/bin/aws s3 cp /mysqlback/mysql_backup/xxxxxxxfull$TODAY.xbstream.gz s3://agt-fantasy-prod-s3-mysql-self-log/mysqlbakup/fantasylr/xxxxxxxfull$TODAY.xbstream.gz