--创建数据库
create database xc_course default character set utf8;
--sql导入
mysql xc_course < xc_course.sql;
--删除数据库
drop database xc_course;
--创建dbuser账号密码为123456,并赋予对xc_course库所有操作,并允许所有主机访问
grant all on xc_course.* to dbuser@'%' identified by '123456';
--创建用户:
CREATE USER 'root'@'%' IDENTIFIED by 'mysql123456';
--查看授权
--查看’root’@’%'的授权:
show grants for 'root'@'%';
--查看授权用户
select * from mysql.user;
--撤销某个用户的授权:
REVOKE ALL on orchestrator.* FROM 'orchestrator_server'@'10.23.211.199';
--删除用户:
delete from mysql.user where user='orchestrator_server' and host='10.23.211.199' ;
--刷新
flush privileges;
use mysql;
select Host,User,Password from user;
--mysql命令参数详解:
-u 用户名
-p 用户密码
-h 服务器IP地址
-D 连接的数据库
-N 不输出列信息
-B 使用tab键代替默认交互分隔符
-e 执行SQL语句
其他选项
-E 垂直输出
-H 以HTML格式输出
-X 以XML格式输出
sh operate_mysql.sh xc_course "insert into course_base (id,name,users,grade) values ("1",'test001','小白','200001')"
operate_mysql.sh
#!/bin/sh
user="dbuser"
password="123456"
host="192.168.72.129"
dbname="$1"
SQL="$2"
mysql -h"$host" -u"$user" -p"$password" -D"$1" -B -e "$2"
--将文本格式的数据导入到mysql中
import_mysql.sh
#!/bin/sh
user="dbuser"
password="123456"
host="192.168.72.129"
mysql_conn="mysql -h"$host" -u"$user" -p"$password""
cat result.txt|while read id name user grade
do
$mysql_conn -e "insert into xc_course.student values('$id','$name','$user','$grade')"
done
import1_mysql.sh
#!/bin/sh
users="dbuser"
password="123456"
host="192.168.72.129"
IFS="|"
cat result1.txt | while read id name user grade
do
mysql -h"$host" -u"$users" -p"$password" -e "insert into xc_course.student1 values('$id','$name','$user','$grade')"
done
create table student1 like student;
备份mysql中的库或表
mysqldump
常用参数详解:
-u 用户名
-p 用户密码
-h 服务器IP地址
-d 等价于--no-data 只导出表结构
-t 等价于--no-create-info 只导出数据,不导出建表语句
-A 等价于--all-databases
-B 等价于--databases 导出一个或多个数据库
mysqldump -u dbuser -p123456 -h 192.168.72.129 xc_course > /tmp/xc_course.sql
mysqldump -u dbuser -p123456 -h 192.168.72.129 xc_course student > /tmp/student.sql
mysqldump -u dbuser -p123456 -h 192.168.72.129 -A > /tmp/all.sql
mysqldump -u dbuser -p123456 -h 192.168.72.129 -B test > /tmp/test.sql
mysqldump -u dbuser -p123456 -h 192.168.72.129 -B xc_course -t> /tmp/xc_table.sql
mysqldump -u dbuser -p123456 -h 192.168.72.129 xc_course -d> /tmp/xc_table.sql
FTP常用命令
open 与ftp服务器建立连接,例子:open 192.168.72.129
user 有权限登录ftp服务器的用户名和密码
ftp.sh
#!/bin/sh
ftp -inv << EOF
open 192.168.72.129
user caoyu caoyu123
cd /tmp
put 1.txt
bye
EOF
mysql_back.sh
#!/bin/sh
db_user="dbuser"
db_password="123456"
db_host="192.168.72.129"
ftp_user="caoyu"
ftp_password="caoyu123"
ftp_host="192.168.72.129"
dst_dir="/data/mysql_back2"
time_date="`date +%Y%m%d%H%M%S`"
file_name="xc_course_student_${time_date}.sql"
function auto_ftp
{
ftp -niv << EOF
open $ftp_host
user $ftp_user $ftp_password
cd $dst_dir
put $1
bye
EOF
}
mysqldump -h$db_host -u$db_user -p$db_password xc_course student > ./$file_name && auto_ftp ./$file_name