mysql

数据库添加索引

ALTER TABLE pub_med ADD INDEX idx_update_time(update_time);
SHOW INDEX FROM pub_med

远程

1. 打开mysql配置文件my.cnf，可以使用命令：sudo vi /etc/mysql/my.cnf;
2. 编辑my.cnf文件，在mysqld部分添加以下内容：
bind-address = 0.0.0.0
# 0.0.0.0表示MySQL服务监听所有的IP地址，允许所有的设备连接MySQL服务。
3. 重新启动MySQL服务（如Ubuntu：sudo service mysql restart）;
4. 进入MySQL，创建远程访问用户，授权其可以在远程主机访问MySQL服务：
# 进入MySQL命令行：
$ mysql -u root -p
# 创建用户和授权语句：
>CREATE USER 'username'@'%' IDENTIFIED BY 'password';
>GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
>FLUSH PRIVILEGES;
# 其中username为你所指定的用户名，password为密码，%表示所有远程IP地址。
5. 启用防火墙（如Ubuntu：sudo ufw enable），打开MySQL端口3306（如Ubuntu：sudo ufw allow 3306）。

mysql 8.0更新用户密码

SELECT user, host FROM mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

alter user root@localhost IDENTIFIED by '123456';

MySQL 8.0 创建用户并为用户给与所有权限

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';
FLUSH PRIVILEGES;
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

create user 'slurm'@'localhost' identified by 'SomePassWD';
update user set host = '%' where user = 'root'; 
select host, user from user;  
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;  
FLUSH PRIVILEGES; 

https://blog.csdn.net/weixin_42782150/article/details/124330167
https://blog.csdn.net/u011277745/article/details/125698071

赋予 testdb数据库的所有权限

grant all on testdb.* to test_user@'%' identified by 'Ab12345';

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'

grant select, insert, update, delete on testdb.* to common_user@'%'

grant 高级 DBA 管理 MySQL 中所有数据库的权限

grant all on *.* to dba@'localhost'

查询用户

select user,host from mysql.user;

新建用户

 create user 'test'@'localhost' identified by '1234';

授予用户test通过外网IP对数据库“testdb”的全部权限

grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234';
--授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限
grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';

刷新权限

flush privileges; 

查看用户权限

show grants for test;

set global validate_password_policy=LOW;
grant all privileges on bioinfo.* to wangyang@'%' identified  by 'xxxxxxx';

Stop mysql

sudo /etc/init.d/mysql stop

Or for other distribution versions:

sudo /etc/init.d/mysqld stop

Reset the password

-- MySQL version < 5.7
update user set password=PASSWORD("mynewpassword") where User='root';

-- MySQL 5.7, mysql.user table "password" field -> "authentication_string"

update user set authentication_string=password('mynewpassword') where user='root';

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

mysql数据迁移

mysqldump -u root -p  cms_dev > cms.db
 scp xxx@xxx:~/cms/cms.db .
 mysql -u wangyang -p
create database cms_dev;