常用命令 · 笔记

### **命令行导入数据(还未验证)** `mysql -h172.26.12.252 -uroot -pcdx9527 ${db} -e "source tony.sql;"` ### **mysqldump导出** `mysqldump -uroot --complete-insert --no-create-info --set-gtid-purged=OFF -p $db >$db.sql` dump出来的sql，里面带有删除表和新建表，也就是说再导入的时候会删除相关表再建立新表新数据 `mysqldump -uroot --complete-insert --no-create-info --set-gtid-purged=OFF -pabcd1234 -d $db >$db.sql` **加参数d，导出的是表结构，不加则导出表结构和数据** # dump出所有库 `mysqldump -h192.168.60.237 -P30001 -uroot -pnHH29kEA7 --all-databases > sqlfile.sql` # 带条件导出 `mysqldump -uleanewg -hrds86s534js41f6ih716.mysql.rds.aliyuncs.com -p trader_ewg ms_real_user --where="email='927059130@qq.com'" >ewg20170424.sql` `SELECT * FROM t_message WHERE to_user = '544034637@qq.com';` # 备份导出数据排除某张表 `mysqldump -uusername -ppassword -h192.168.0.1 dbname --ignore-table=dbname.dbtable1 --ignore-table=dbname.dbtable2> dump.sql` # 备份导出某库某表 `mysqldump -uroot -hrm-j6cmjlnpq40r97l15741.mysql.rds.aliyuncs.com -p'nIg96t^ZO*)1l4FD' --set-gtid-purged=OFF brokerwork_t001182_mt4_1 mt4_trades > mt4_trades.sql` # 导出备份mysql数据的一些参数 ``` mysqldump -uadmin -hlw-mysql.c7wcngguwxc4.ap-east-1.rds.amazonaws.com -p'Nhy6bgt5vfr4!' --set-gtid-purged=OFF --single-transaction brokerwork_reportfile > brokerwork_reportfile_20210829.sql # --set-gtid-purged=OFF 加了--set-gtid-purged=OFF时，在会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加--set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。 # --opt选项是默认选项 # --skip-opt 备份数据库时会将数据备份成一条一条的insert 但是实际在导出的时候主键自增这个，导出的数据不会有，如下面第2条。 CREATE TABLE `rules` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, CREATE TABLE `rules` ( `id` bigint(20) NOT NULL, # master-data 这个参数是记录导出的位置，所以断了网仍然可以导出，默认参数为1，即开启这个功能。 --master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行； # 对于支持事务的引擎如 InnoDB ，参数上是在备份的时候加上 –single-transaction 保证数据一致性 –single-transaction 实际上通过做了下面两个操作： ① 在开始的时候把该 session 的事务隔离级别设置成 repeatable read ； ② 然后启动一个事务（执行 begin ），备份结束的时候结束该事务（执行 commit ）可以理解为对于 innodb 引擎来说加了该参数，备份开始时就已经把要备份的数据定下来了，备份过程中的提交的事务时是看不到的，也不会备份进去。对于不支持事务的引擎如 MyISAM ，只能通过锁表来保证数据一致性 ``` > ### **mysqldump没有排除某个库的功能** ### **mysql导出为csv** 方法一、 mysql自带的命令，into outfile，可以按照需要的文件格式进行导出，实现效率很快，但是产生的文件只会导出到mysql所在的终端上 ``` ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。 secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。 secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。 secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。 ###### mysql> show global variables like '%secure_file_priv%'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec) ##### mysql>use brokerwork_t002207_mt4_trades; mysql> select * from mt4_trades into outfile '/var/lib/mysql-files/2207.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n'; ``` 方法二、 `mysqldump -h172.16.81.236 -uusername -ppassword -t -T/tmp/waring.csv dbname collection --fields-enclosed-by=\" --fields-terminated-by=, ` 方法三、 ``` mysql -uroot -hrm-j6cmjlnpq40r97l15741.mysql.rds.aliyuncs.com -p'nIg96t^ZO*)1l4FD' --default-character-set=gbk brokerwork_t001182_mt4_1 -e 'select * from mt4_trades;' | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/file.csv ``` ### **授权** ``` GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'broker_user'@'%' identified by '9vQSZ1KhD2Hc7Ebv3108oQ=='; ``` ### 查询表数据 ``` select * from test_limit limit 1; 查询头一条数据 select * from test_limit order by id DESC limit 1; #查询最后一条数据 ``` ### 删除一个user `DROP USER 'jeffrey'@'localhost';`