Shulou(Shulou.com)06/01 Report--

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

SQL backups need to be imported into Mysql, but what if the SQL file is too large?

Recommend a shell script: mysqldumpsplitter

Option:-source: mysqldump filename to process. It could be a compressed or regular file.--desc: This option will list out all databases and tables.--extract: Specify what to extract. Possible values DB, TABLE, ALLDBS, ALLTABLES, REGEXP--match_str: Specify match string for extract command option.--compression: gzip/pigz/bzip2/none (default: gzip). Extracted file will be of this compression.--decompression: gzip/pigz/bzip2/none (default: gzip). This will be used against input file.--output_dir: path to output dir. (default:. / out/)-- config: path to config file. You may use-- config option to specify the config file that includes following variables. SOURCE= EXTRACT= COMPRESSION= DECOMPRESSION= OUTPUT_DIR= MATCH_STR=mysqldumpsplitter recipes: extract a single database from mysqldump: sh mysqldumpsplitter.sh-- source filename-- extract DB-- match_str database-name the command above will create a sql for the specified database from the specified "filename" sql file and store it in a compressed format to database-name.sql.gz. Extract a single table from mysqldump: the command above sh mysqldumpsplitter.sh-- source filename-- extract TABLE-- match_str table-name creates a sql for the specified table from the specified "filename" mysqldump file and stores it in a compressed format to database-name.sql.gz. Extract the table that matches the regular expression from mysqldump: the command above sh mysqldumpsplitter.sh-- source filename-- extract REGEXP-- match_str regular-expression creates a sqls for the table of the specified regular expression in the specified "filename" mysqldump file and stores it in a single table-name.sql.gz in a compressed format. Extract all databases from mysqldump: the command above sh mysqldumpsplitter.sh-- source filename-- extract ALLDBS will extract all databases from the specified "filename" mysqldump file and store them in a single database-name.sql.gz in a compressed format. Extract all tables from mysqldump: the command above sh mysqldumpsplitter.sh-- source filename-- extract ALLTABLES will extract all tables from the specified "filename" mysqldump file and store them in a single table-name.sql.gz in a compressed format. Extract the list of tables from mysqldump: sh mysqldumpsplitter.sh-- source filename-- extract REGEXP-- match_str'(table1 | table2 | table3) 'the command above will extract tables from the specified "filename" mysqldump file and store them in a single table-name.sql.gz in a compressed format. Extract the database from the compressed mysqldump: sh mysqldumpsplitter.sh-- source filename.sql.gz-- extract DB-- match_str 'dbname'-- decompression gzip the command above will extract the filename.sql.gz using gzip Extract the database named "dbname" from "filename.sql.gz" and store it as out / dbname.sql.gz to extract the database from the compressed mysqldump in a compressed format: sh mysqldumpsplitter.sh-- source filename.sql.gz-- extract DB-- match_str 'dbname'-- decompression gzip-- the command above will extract filename.sql.gz using gzip and extract the data named "dbname" from "filename.sql.gz". Library and store it as plain sql out / dbname.sql to extract alltables:sh mysqldumpsplitter.sh from mysqldump in different folders-- source filename-- extract ALLTABLES-- output_dir / path/to/extracts/ the command above will extract all tables from the specified "filename" mysqldump file The compressed table is extracted to each file table-name.sql.gz stored in / path / to / extracts /. The script will create the folder / p ​​ ath / to / extracts / if it doesn't exist. Extract one or more tables from a database in a full dump: consider that you have a full dump that contains multiple databases, and you want to extract a small number of tables from one database. Extract single database: `sh mysqldumpsplitter.sh-- source filename-- extract DB-- match_str DBNAME-- compression none`Extract all tables`sh mysqldumpsplitter.sh-- source out/DBNAME.sql-- extract REGEXP-- match_str "(tbl1 | tbl2)" `although we can use another option to do this in a single command As follows: sh mysqldumpsplitter.sh-- source filename-- extract DBTABLE-- match_str "DBNAME. (tbl1 | tbl2)"-- the command above compression none extracts tbl1 and tbl2 from the DBNAME database in sql format under the folder "out" in the current directory. You can extract individual tables as follows: sh mysqldumpsplitter.sh-- source filename-- extract DBTABLE-- match_str "DBNAME. (tbl1)"-- compression none extracts all tables from a particular database: mysqldumpsplitter.sh-- source filename-- extract DBTABLE-- match_str "DBNAME.*"-- the command above compression none will extract all tables from the DBNAME database in sql format and store them in the "out" directory. List the contents of the mysqldump file mysqldumpsplitter.sh-- source filename-- desc the command above lists the databases and tables in the dump file.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.