Linux: RDS迁移自建操作步骤

timedatectl statusmysql_replication_hostgroups
timedatectl set-timezone "Asia/Shanghai"

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') ;

-- 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' ;

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

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;

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'read_only','w r group');

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;

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;

继续往下添加/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

sudo wget https://files.pythonhosted.org/packages/d3/7f/c780b7471ba0ff4548967a9f7a8b0bfce222c3a496c3dfad0164172222b0/supervisor-4.2.2.tar.gz
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

sudo mv mysqld_exporter /devops/mysqld_exporter/

sudo mv node_exporter /devops/nodexporter/

sudo mkdir  /devops/proxysqlexporter
sudo cp proxysql_exporter /devops/proxysqlexporter/

# sudo vim /etc/supervisor/conf.d/proxysql_exporter.conf
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 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;

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;

#!/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;

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