mysql 常用命令 及数据备份和恢复

查看某个表的结构

 如下例为查看表VM的数据结构

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, COLUMN_TYPE,
     COLUMN_KEY  from information_schema.columns where table_name = 'VM'

 

更新表1的数据列值为表2的某个数据列值

 UPDATE TOKEN INNER JOIN VM ON (VM.ID = TOKEN.ID) SET TOKEN.HOST_IP = VMF.IP;

left join 查询

SELECT dst.*, org.NAME  FROM DEST as dst LEFT JOIN ORGANIZATION as org on dst.ORG_ID = org.ORGANIZATION_ID; 

删除两列

ALTER TABLE table1 DROP COLUMN ip, DROP COLUMN port;

 

计算时间列的差异： 

SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) diff;

 

重置mysql密码

 sudo service mysql stop
 sudo mysqld_safe --skip-grant-tables
 sudo service mysql start
 sudo mysql -u root
 use mysql;
 show tables;
 describe user;
 update user set authentication_string=password('1111') where user='root';
 FLUSH PRIVILEGES;

设置mysql账号密码

SELECT user,authentication_string FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass_here';
FLUSH PRIVILEGES;
SELECT user,authentication_string FROM mysql.user;

 

备份与恢复数据库

 参考：

     https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost

     https://www.sqlshack.com/how-to-backup-and-restore-mysql-databases-using-the-mysqldump-command/

mysqldump -uroot -p --all-databases > db0625.sql
mysql  -uroot -ppassword -h 172.0.0.1
create database: dbAbc 
//import data
mysql  -uroot -ppassword dbAbc < ~/db0625.sql