Import Mysql Data in shell scripts & execute MySQL statements

Preface when you perform Mysql Data Analysis Online, you may encounter the situation of importing SQL files to the MySQL database by executing shell scripts or connecting MySQL to execute specified SQL statements, here we will introduce how to import SQL files to the MySQL database.

Sample Code

# Variable definition sqlname = "test. SQL "dir ="/sdb2/backup/mysql_db_backup/backup/databases "host =" 127.0.0.1 "user =" root "passwd =" 123456 "dbname =" test "# import an SQL File to the specified database mysql-h $ host-U $ user-p $ passwd $ dbname <$ DIR/$ sqlname

Key Point "<" operator used to execute the specified SQL statement mysql-e Parameter Interpretation

 --execute=statement, -e statementExecute the statement and quit. The default output format is like that produced with --batch

--silent, -sSilent mode. Produce less output. This option can be given multiple times to produce less and less output.

Sample Code

select_sql="select count(distinct id) from tb_test"num=$(mysql -s -h$host -u$user -p$passwd $dbname -e "$register_sql")

Note:

• -S parameter is used to reduce the output of the query field (PS: Here I only need to query the result value, and do not need to query the field name. Without the-S parameter, the query field name will be output)

MPs queue Operator

echo "select count(distinct id) from tb_test" | mysql -h$host -u$user -p$passwd $dbname

PS: I suggest using the-e parameter for more convenience.

Mysqldump export Mysql Data export database with specified conditions
Command Format

Mysqldump-u user name-P password-H host database name table name -- where "SQL statement"> path

Sample Code

#! /Bin/bash # variable definition host = "127.0.0.1" user = "root" passwd = "123456" dbname = "test" tablename = "tb_test" mysqldump-U $ user-p $ passwd-h $ host $ dbname $ tablename -- where "id> 1 and ID <1000"> 1. SQL

Export a data structure (no data) parameter

--no-data, -dDo not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).

Command Format

Mysqldump-u username-P password-H host database name-D> path

Sample Code

#! /Bin/bash # variable definition host = "127.0.0.1" user = "root" passwd = "123456" dbname = "test" mysqldump-U $ user-p $ passwd-h $ host $ dbname-D> 2. SQL

Reference http://blog.linezing.com/2012/02/mysql%E5%AF%BC%E5%85%A5%E5%AF%BC%E5%87%BA%E6%95%B0%E6%8D% AE %E6%96%B9%E6%B3%95