SHELL：导出，并导入MySQL
脚本：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
# auther : adamhuan # blog : www.d-prototype.com # script : etl_mysql_by_mysqldump_where.sh # deploy on : third machine # usage: # node 1 --- MySQL DUMP data source # node 2 --- MySQL DUMP data target # variable # ------> 后面，这一部分用配置文件配置 # mysql, source / target: # grant all privileges on *.* to root@'%' identified by 'xxxx' # mysql source mysql_source_ip="***********" mysql_source_port="3306" mysql_source_user="root" mysql_source_password="********" mysql_source_db="me" # mysql target mysql_target_ip="********" mysql_target_port="3306" mysql_target_user="root" mysql_target_password="***********" # mysqldump # time.1 mysqldump_where_command=" id<2" mysqldump_dump_data_dir="/dump_data" mysqldump_dump_full_name="$mysqldump_dump_data_dir/source_dump_data_1.dump" # function function mysql_do_exp_source(){ mysqldump -u $mysql_source_user -p"$mysql_source_password" -h $mysql_source_ip -P $mysql_source_port $mysql_source_db --where="$mysqldump_where_command" > $mysqldump_dump_full_name } function mysql_do_imp_target(){ mysql -u $mysql_target_user -p"$mysql_target_password" -h $mysql_target_ip -P $mysql_target_port $mysql_source_db < $mysqldump_dump_full_name } # running # do source export mysql_do_exp_source # do target import mysql_do_imp_target # finished echo "Finished" |
脚本部署在源与目标端之外的第三方。
开始前：
源：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
[root@rhcs1 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.19-17 Percona Server (GPL), Release 17, Revision e19a6b7b73f Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | me | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use me; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------+ | Tables_in_me | +--------------+ | personal | +--------------+ 1 row in set (0.00 sec) mysql> select * from personal; +------+------------+-----------+ | id | name | other | +------+------------+-----------+ | 1 | Wu Liang | Shang Hai | | 2 | Lenka Liu | Shang Hai | | 3 | Jony Xu | Shang Hai | | 4 | Pony Wu | Shang Hai | | 5 | Qiao Hai | Shang Hai | | 6 | Ying Ru | Shang Hai | | 7 | ShaoNan xu | Shang Hai | +------+------------+-----------+ 7 rows in set (0.00 sec) mysql> |
目标：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[root@rhcs3 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.7.19-17 Percona Server (GPL), Release 17, Revision e19a6b7b73f Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) mysql> create database me; Query OK, 1 row affected (0.00 sec) mysql> mysql> exit Bye [root@rhcs3 ~]# |
第三方：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[root@rhcs2 script]# pwd /script [root@rhcs2 script]# [root@rhcs2 script]# ls -ltr 1.sh -rw-r--r-- 1 root root 1290 Oct 16 18:08 1.sh [root@rhcs2 script]# [root@rhcs2 script]# cat 1.sh | grep --color where_command= mysqldump_where_command=" id<2" [root@rhcs2 script]# [root@rhcs2 script]# cat 1.sh | grep --color dump_data_dir= mysqldump_dump_data_dir="/dump_data" [root@rhcs2 script]# [root@rhcs2 script]# cat 1.sh | grep --color dump_full_name= mysqldump_dump_full_name="$mysqldump_dump_data_dir/source_dump_data_1.dump" [root@rhcs2 script]# [root@rhcs2 script]# ls -ltr /dump_data total 0 [root@rhcs2 script]# [root@rhcs2 script]# |
可以看到，当前的脚本，只是选择性的，导出了【id】小于2的数据。
开始：
第三方：
|
1 2 3 4 5 6 7 8 9 10 |
[root@rhcs2 script]# sh 1.sh mysqldump: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. Finished [root@rhcs2 script]# [root@rhcs2 script]# ls -ltr /dump_data/ total 4 -rw-r--r-- 1 root root 2960 Oct 16 18:26 source_dump_data_1.dump [root@rhcs2 script]# [root@rhcs2 script]# |
成功后，查看下这时候目标端的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
[root@rhcs3 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 5.7.19-17 Percona Server (GPL), Release 17, Revision e19a6b7b73f Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | me | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use me; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------+ | Tables_in_me | +--------------+ | personal | +--------------+ 1 row in set (0.00 sec) mysql> select * from personal; +------+----------+-----------+ | id | name | other | +------+----------+-----------+ | 1 | Wu Liang | Shang Hai | +------+----------+-----------+ 1 row in set (0.00 sec) mysql> |
将导出条件扩大到【id<4】，然后再执行一次：
|
1 2 3 4 5 6 7 8 |
[root@rhcs2 script]# cat 1.sh | grep --color where_command= mysqldump_where_command=" id<4" [root@rhcs2 script]# [root@rhcs2 script]# sh 1.sh mysqldump: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. Finished [root@rhcs2 script]# |
看看目标端的结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[root@rhcs3 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 Server version: 5.7.19-17 Percona Server (GPL), Release 17, Revision e19a6b7b73f Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use me Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from personal; +------+-----------+-----------+ | id | name | other | +------+-----------+-----------+ | 1 | Wu Liang | Shang Hai | | 2 | Lenka Liu | Shang Hai | | 3 | Jony Xu | Shang Hai | +------+-----------+-----------+ 3 rows in set (0.00 sec) mysql> exit Bye [root@rhcs3 ~]# |
———————————————————
Done。
