mysql常用命令

mysql常用命令

mysql导出csv文件

1) 设置my.cnf参数, 需要重启数据库

在mysqld下面添加参数: secure_file_priv=''

2) 导出

select * from t into outfile '/home/mysql/t3.txt'  fields terminated by ',';

3)导入

load data infile “/home/mysql/t3.txt” into table t fields terminated by ‘,’;

mysql恢复备份

备份表结构：

mysqldump
–login-path=vml --opt --default-character-set=utf8 --skip-disable-keys
–hex-blob --quote-names --max_allowed_packet=16777214 --no-data
–skip-add-locks --skip-lock-tables --databases $DATABAS{E}_{N}AME--log-error=${LOGFILE} > schema_dump.sql

备份数据

备份的都是insert脚本, 建议按表备份.
mysqldump --login-path=vml --opt
–default-character-set=utf8 --skip-disable-keys --hex-blob --quote-names
–max_allowed_packet=16777214 --no-create-db --skip-add-drop-table
–no-create-info --complete-insert --skip-extended-insert --skip-add-locks --skip-lock-tables --databases ${DATABASE_NAME} --tables
$TABL{E}_{N}AME--log-error=${LOGFILE

$BACKU{P}_{P}ATH/${DATABASE_NAME}_KaTeX parse error: Expected group after '_' at position 13: {TABLE_NAME}_̲{CDATE}_data_dump.sql

逻辑恢复

mysql远程登录
免密登录
mysql --login-path=vml
Source schema_dump.sql
Source $BACKU{P}_{P}ATH/${DATABASE_NAME}_KaTeX parse error: Expected group after '_' at position 13: {TABLE_NAME}_̲{CDATE}_data_dump.sql

1. 更新user表数据
2. 直接授权
3. 分权限用户readonly_user, rw_user
   备份恢复,sql文件导入新数据库, 同步数据

-- mysql同步数据
ls chaindb*data_dump.sql|awk '{print "source ",$1}' > chaindba_data.sql
ls nacos*data_dump.sql|awk '{print "source ",$1}' > nacos_data.sql

mysqllogin 
use chaindb
source chaindba_2022-03-09_schema_dump.sql
source chaindba_data.sql

use nacos
source nacos_2022-03-09_schema_dump.sql
source nacos_data.sql

mysql导出csv文件
https://blog.csdn.net/chj_1224365967/article/details/118080715

1. 设置my.cnf参数, 需要重启数据库
   在mysqld下面添加参数: secure_file_priv=‘’
2. 导出
   select * from t into outfile ‘/home/mysql/t3.txt’ fields terminated by ‘,’;
   3)导入
   load data infile “/home/mysql/t3.txt” into table t fields terminated by ‘,’;

mysql恢复备份

备份表结构：
mysqldump
–login-path=vml --opt --default-character-set=utf8 --skip-disable-keys
–hex-blob --quote-names --max_allowed_packet=16777214 --no-data
–skip-add-locks --skip-lock-tables --databases $DATABAS{E}_{N}AME--log-error=${LOGFILE} > schema_dump.sql

备份数据

备份的都是insert脚本, 建议按表备份.
mysqldump --login-path=vml --opt
–default-character-set=utf8 --skip-disable-keys --hex-blob --quote-names
–max_allowed_packet=16777214 --no-create-db --skip-add-drop-table
–no-create-info --complete-insert --skip-extended-insert --skip-add-locks --skip-lock-tables --databases ${DATABASE_NAME} --tables
$TABL{E}_{N}AME--log-error=${LOGFILE

$BACKU{P}_{P}ATH/${DATABASE_NAME}_KaTeX parse error: Expected group after '_' at position 13: {TABLE_NAME}_̲{CDATE}_data_dump.sql

逻辑恢复

逻辑备份可以执行在mysql中用source执行
登录
mysql --login-path=vml
Source schema_dump.sql
Source $BACKU{P}_{P}ATH/${DATABASE_NAME}_KaTeX parse error: Expected group after '_' at position 13: {TABLE_NAME}_̲{CDATE}_data_dump.sql

备份恢复,sql文件导入新数据库, 同步数据
– mysql同步数据
ls chaindbdata_dump.sql|awk ‘{print "source ",$1}’ > chaindba_data.sql
ls nacosdata_dump.sql|awk ‘{print "source ",$1}’ > nacos_data.sql

mysqllogin
use chaindb
source chaindba_2022-03-09_schema_dump.sql
source chaindba_data.sql

use nacos
source nacos_2022-03-09_schema_dump.sql
source nacos_data.sql
mysql恢复到时间点
按时间点恢复数据
mysqlbinlog --start-datetime=“2022-02-08 16:30:00” --stop-datetime=“2022-02-08 16:59:59” mysql-bin.000005 -r test2.sql

my.cnf开启binlog, 设置模式row, 日志过期时间15天
在[mysqld] 区块设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；
log-bin=mysql-bin
binlog_format=row
expire_logs_days=15

mysql远程登录

1. 更新user表数据
   use mysql;
   select host from user where user=‘root’;
   update user set host = ‘%’ where user =‘root’;
   flush privileges;

2. 直接授权
   GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘Mgmysql@666’ WITH GRANT OPTION;
   GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ IDENTIFIED BY ‘Mgmysql@666’ WITH GRANT OPTION;
   flush privileges;

3. 分权限用户readonly_user, rw_user
   create user readonly_user IDENTIFIED BY ‘Gglg!888’ ;
   GRANT USAGE ON . TO ‘readonly_user’@‘localhost’ IDENTIFIED BY ‘Gglg!888’ ;
   grant select on . to ‘readonly_user’@‘localhost’ IDENTIFIED BY ‘MgGglglg!888’ ;
   grant select on . to ‘readonly_user’@‘%’ IDENTIFIED BY ‘Mglg!888’ ;

create user rw_user IDENTIFIED BY ‘Gglg@666’ ;
GRANT USAGE ON . TO ‘rw_user’@‘localhost’ IDENTIFIED BY ‘Gglg@666’ ;
grant select,insert, update, delete,EXECUTE on . to ‘rw_user’@‘Gglg’ IDENTIFIED BY ‘Mglg@666’ ;
grant select,insert, update, delete,EXECUTE on . to ‘rw_user’@‘%’ IDENTIFIED BY ‘MgGglglg@666’ ;
flush privileges;