Written in front

Mysql is a magical relational database. I really feel that it's awesome. Because the projects I've done are quite miscellaneous, I've met Oracle database before, which gives me the impression that Oracle is very cumbersome and cumbersome, with many configurations. If it's used for small projects, it's like killing chickens and using cattle knives, big materials and small uses. But it's not that Mysql can't be used in large projects. Mysql is free at the beginning of the year. It's now the mainstream product of relational databases. There are many corresponding documents and problem-solving solutions on the Internet, which means that novices like me have encountered problems with Mysql, and the Internet is basically complete.

System environment Debain 7
Mysql 5.6

1.MySQL simple operation command

//1. Log in to mysql, optional in brackets, $(including $) followed by actual data, - D is the specified database login
mysql (-h$host) (-P$port) -u$user -p$pwd (-D$dbname)  //Address port account password database name

//2. Delete and create database
drop database dbname; //Delete database
create database dbname charset utf8   //Create database

//3. Delete and create database tables
drop table tablename;//Delete table
create table tablename(id int, name varchar(80)); //Create table

//4. table operation
show triggers\g / show triggers; //View triggers
show variables like 'character_set_database'; //View library code
desc tablename; //View table structure
select current_date(); //View table creation time

//5. Import sql file
use dbname;source /dbname.sql; //Execute sql file

//6. Current connection
select current_user(); //View current login account
show processlist; //View current process
show full processlist;//View all current processes
select user,host,Super_priv from mysql.user; //View all accessible user, address, and permission information
(Super_priv Users have super Permission to import data)
grant all privileges on *.* to root@'%' identified by 'root' with grant option;flush privileges; //All permissions for remote login to root

2. Automatically import sql file

2.1 shell operation

#Create createDb.sh as follows:

#!/bin/bash
#Automatic initialization of database and table structure by shell
host=$1 #address
port=$2 #port
user=$3 #Account number
pwd=$4 #Password
dbname=$5 #Database name
path=$6 #sql file path

mysql -h$host -P$port -u$user -p$pwd <<EOF

drop database if exists $dbname;create database $dbname charset utf8;

use $dbname;

source $path

COMMIT;
EOF

#View shell execution commands
sh -x ./shell //View shell execution

2.2 expect operation

#!/usr/bin/expect -f

set timeout 10
set host [lindex $argv 0]
set port [lindex $argv 1]
set user [lindex $argv 2]
set pwd [lindex $argv 3]
set dbname [lindex $argv 4]
set path [lindex $argv 5]
set cset [lindex $argv 6] #Character encoding

spawn mysql -h$host -P$port -u$user -p
expect "Enter password: "
send "$pwd\r"

expect "mysql> "
send "drop database if exists $dbname;create database $dbname charset $cset;\r"

expect "mysql> "
send "use $dbname;\r"

expect "mysql> "
send "source $path;\r"

expect "mysql> "
send "exit\r"

interact

If you want to study and update later, please let me know what is wrong