shell操作mysql

发布时间:2021-08-03 22:12:19 阅读:1266次

root@localhost ~ # mysql -uroot -p -h127.0.0.1 test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22123
Server version: 5.1.60-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysqldump –add-drop-table –extended-insert –force –log-error=error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost “mysql -uUSER -pPASS NEW_DB_NAME”通过压缩的SSH隧道Dump一个MySQL数据库,将其作为输入传递给mysql命令,我认为这是迁移数据库到新服务器最快最好的方法。

 1920  mysql -uroot -p123456 -P3306 -e create database test

 1922  mysql -uroot -p123456 -P3306 -e "drop database test"
 1927  mysql -uroot -p123456 -P3306 test -e "create table test(id int,username varchar(50)"
 1945  mysql -hlocalhost -P3306 -uroot -p123456 -Dtest -e "insert into test values(2,'test')"
 1951  mysql -uroot -p123456<<EOF
 1952  use aabb;
 1953  create table test(id int,username varchar(50))
 1954  EOF
 1974  mysql -uroot -p123456 -e "select *from aabb.v9_admin"
 1975  echo "select *from aabb.v9_admin"|mysql -uroot -p123456
mysql导出csv(注意权限) 
select *from v9_picture_favorite where 1 and disabled='false' order by id desc limit 100 into outfile '/var/www/html/test/report2.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
 
select *from v9_picture_favorite where 1 and disabled='false' order by id desc limit 1 into outfile 'd:/report3.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
 
root@test:/var/www/html# /home/test/mongodb/bin/mongoimport -d aabb -c v9_picture_favorite --type csv -f id,classid,source_from,userid,username,real_name,mobile,areaname,ip_area,email,regdate,tuku_url,title,keyword,pic_url,pic_url_nowater,add_memo,add_num,ip,disabled,addtime,flag,water_flag   --file /var/www/html/fav.csv
 
mysql导入csv 
load data infile '/var/www/html/test/report2.csv' into table test fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
显示mysql
test@tuku:~$  mysql -uroot -p123456 -N -e 'select * from aabb.v9_picture_favorite limit 10' 
test@tuku:~$  mysql -uroot -p123456 -N -e 'select * from aabb.v9_picture_favorite limit 10\G'
 
导出mysql
[root@tuku ~]# mysql -uroot -p123456 -e "select * from aabb.v9_picture_favorite into outfile '/tmp/mysql.txt' fields terminated by ',' enclosed by '\"' lines terminated by '\r\n'";
 
导入mysql
mysql -uroot -ppassword 数据库名 < update.sql

如有问题,可以QQ搜索群1028468525加入群聊,欢迎一起研究技术

支付宝 微信

有疑问联系站长,请联系QQ:QQ咨询

转载请注明:shell操作mysql 出自老鄢博客 | 欢迎分享