参考官方文档:6.2 Access Control and Account Management(https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/index.html)
查看命令:
windows环境:[mysql]为服务的名称,有可能是mysql57.
》mysqld --user=mysql & (启动)
》mysqladmin -u root shutdown
服务注册后
》net stop [mysql]
》net start [mysql]
》net restart [mysql]
》service mysqld start //启动
》service mysqld restart //重启
》service mysqld stop // 停止
>mysql -u root -p //登录
>mysql -u root -p [database] //登录,并指定database
>mysql -u root --skip-password //登录越过密码,适用于初始化登录
>bin\mysql -h localhost -P 3307 -u root -proot123 //登录指定host和port,输入账号密码
> show global variables like "wait_timeout"; //查看超时设置时间
》show VARIABLES like 'auto_inc%'; //查看步长
》show VARIABLES like '%version%'; //查看DB版本
》show VARIABLES like '%lower%'; //查看是否大小写敏感
》show variables like '%slow%'; //查看慢sql日志记录是否开启,以及日志记录的名字,即duration。
>show index from [tablesname];
>show keys from [tablesname];
>show databases; //看DB库
>use mysql; //切换到库核心库
>describe [tablename]; //表信息
参照:https://dev.mysql.com/doc/mysql-getting-started/en/
>SELECT * FROM mysql.user; //查看所有用户信息
> select host,user from user where user='用户名';
>CREATE USER canal IDENTIFIED BY 'canal'; //创建用户和密码
》alter user [username] identified with mysql_native_password by '[password]'; //修改为支持mysql5版本的加密方式(mysql_native_password)
》alter user [username] identified by [password]; // 不进行加密的修改密码方式
》UPDATE user SET Password=PASSWORD('123456') where USER='root'; // 如果提示password不存在,则使用
UPDATE USER SET authentication_string=password('123456') where USER='root';
》FLUSH PRIVILEGES;
>grant all privileges on [dbname].* to [username];
>grant select,update on [dbname].* to [username];
>flush privileges;
>source [filepath] //导入sql
>GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; //设置权限
》update user set host = ’%’ where user = ’root’;
>set global character_set_database=utf8; //修改设置字符集注意是utf8,非utf-8
》set character_set_client=utf8;
》set character_set_connection=utf8;
》set character_set_results=utf8;
》set character_set_server=utf8;
然后重启服务。
对于set character_set_server=utf8; 即使修改重启也会变为 latin,
因此windows下修改my.ini,
linux下文件名为 /etc/my.cnf 在文件中添加
default-character-set=utf8 这个会让系统启动不起来
character-set-server=utf8
然后重启 mysql服务即可。