Export/import MySQL DB in multi threads with mydumper/myloader

To Nha Notes | May 4, 2021, 11:06 a.m.

Installation

• Requirements

  yum -y groupinstall "Development Tools"
  yum install glib2-devel mysql-devel zlib-devel pcre-devel
  wget https://cmake.org/files/v3.18/cmake-3.18.0.tar.gz
  tar -xvzf cmake-3.18.0.tar.gz 
  cd cmake-3.18.0 
  ./bootstrap
  make 
  sudo make install
  

• Enable receive packet steering (RPS)

  sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo FFF > $x/rps_cpus; done' 
  sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo 4096 > $x/rps_flow_cnt; done'
  sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt 
  sh -c "echo 4096 > /sys/class/net/eth0/rps_flow_cnt"
  

• Enable receive flow steering (RFS)

  sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
  

• Install Mydumper

  wget https://github.com/maxbube/mydumper/archive/v0.9.5.tar.gz
  tar xzvf v0.9.5.tar.gz 
  cd mydumper-0.9.5/
  cmake .
  make
  make install
  

Create backup/restore folders

mkdir -p /var/local/schema/
chmod 777 /var/local/schema/
mkdir -p /var/local/data/
chmod 777 /var/local/data/

Export data

• Export schema only

  /usr/local/bin/mydumper --host=<host> --user=<user> --password=<pass> --outputdir=/var/local/schema/ --database=<db>--statement-size=1000000 --chunk-filesize=50 --long-query-guard=600 --snapshot-interval=1440 --insert-ignore --compress --build-empty-files --threads=2 --compress-protocol --no-locks --no-data --verbose 3 --logfile=/var/log/dumps.log

• Export data only

  /usr/local/bin/mydumper --host=<host> --user=<user> --password=<pass> --outputdir=/var/local/data/ --database=<db> --statement-size=1000000 --chunk-filesize=50 --long-query-guard=600 --snapshot-interval=1440 --insert-ignore --compress --build-empty-files --threads=2 --compress-protocol --no-locks --no-schemas --verbose 3 --logfile=/var/log/dumps.log

Import data

• Import schema only

  /usr/local/bin/myloader --source-db=<source-db> --database=<target-db> --host=<target-host>--user=<target-user> --password=<target-pass> --directory=/var/local/schema/ --queries-per-transaction=1000 --threads=2 --compress-protocol --verbose=3 --overwrite-tables

• Import data only

  mysql> SET GLOBAL group_concat_max_len=<source rds value>;
  mysql> SET GLOBAL max_allowed_packet=<source rds value>;
  mysql> SET GLOBAL net_read_timeout=3600; 
  mysql> SET GLOBAL net_write_timeout=3600;
  

  /usr/local/bin/myloader --source-db=<source-db> --database=<target-db> --host=<target-host> --user=<target-user> --password=<target-pass> --directory=/var/local/data --queries-per-transaction=10 --threads=2 --compress-protocol --verbose=3

Migrate MySQL accounts

mysql -B -N --host=<source-host> --user=<source-user> --password=<source-pass> -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user in(nlp', 'medica', 'qa', 'rebot', 'tennis', 'vsuser', 'virtualspaces')" mysql > mysql_all_users.txt

while read line; do mysql -B -N --host=<source-host> --user=<source-user> --password=<source-pass> -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

sed -i 's/$/;/' mysql_all_users_sql.sql

mysql --host=<source-host> --user=<source-user> --password=<source-pass> < mysql_all_users_sql.sql

Update authentication

mysql --host=<target-host> --user=<target-user> --password=<target-pass>
mysql> use mysql;
mysql> update user set authentication_string=xxx where user=’user1’;
mysql> update user set authentication_string=xxx where user=’user2’;
mysql> update user set authentication_string=xxx where user=’user3’;
mysql> FLUSH PRIVILEGES;