MySQL的那点事

创建用户，数据库，表

CREATE USER ROOT;

CREATE DATABASE Evan;

USE Evan;

CREATE TABLE 表名 (字段设定列表);

grant 授权

GRANT ALL ON *.* TO 'root'@'%';

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* to test@"%" IDENTIFIED BY "abc";

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slaver'@'%' IDENTIFIED BY 'Iuu';

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS, SUPER, CREATE, INSERT, SELECT ON *.* TO xtraback@'localhost' IDENTIFIED BY 'xtraback';

删除用户、库

mysql> DROP USER "root"@"::1";

mysql> DROP USER ""@"localhost";

mysql> DROP database test;

修改用户密码

#MySQL 5.7 
update mysql.user set authentication_string=password("Evan.@xyz") where user="root" and host='%';

#MySQl 5.6
SET PASSWORD FOR 'Evan'@'localhost' = PASSWORD('mypass');

• grant授权和revoke回收权限
• MySQL之权限管理

REPLICATION SLAVE 与 REPLICATION CLIENT 权限

REPLICATION CLIENT权限可以使用 SHOW MASTER STATUS, SHOW SLAVE STATUS和 SHOW BINARY LOGS来确定复制状态。(5.6.6版本以后才可以使用 SHOW BINARY LOGS )

授予复制账号REPLICATION SLAVE权限，复制才能真正地工作。并且"SHOW SLAVE HOSTS"这条命令和REPLICATION SLAVE权限有关。

CHANGE MASTER TO 相关

CHANGE MASTER TO MASTER_HOST ='10.*.*.*', MASTER_USER='slaver', MASTER_PORT=3006, MASTER_PASSWORD='itchao.win', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;

CHANGE MASTER TO MASTER_HOST='192.168.199.82', MASTER_USER='slaver', MASTER_PORT=3006, MASTER_PASSWORD='itchao.win',MASTER_AUTO_POSITION=1;

mysql> CHANGE MASTER TO \
    -> MASTER_HOST='127.0.0.1', \
    -> MASTER_PORT=3006, \
    -> MASTER_USER='replication', \
    -> MASTER_PASSWORD='Evan.2018', \
    -> MASTER_LOG_FILE='mysql-bin.000001', \
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

#开启主从并查询
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G
************ 1. row ************
.........................
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示主从正常
Seconds_Behind_Master: 0
表示主从没有延迟
.........................

其它

#查询用户
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| develop       | %         |
| replication   | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

#查询登录了那些用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

#查询版本
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)


#查询用户授权
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for develop;
+-------------------------------------------------------------------------------+
| Grants for develop@%                                                          |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'develop'@'%'                                           |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `xxxxx`.* TO 'develop'@'%'            |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `xxxxx_bala`.* TO 'develop'@'%'       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `xxxxxx_shop`.* TO 'develop'@'%'      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `xxxxxx_message`.* TO 'develop'@'%'   |
+-------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> show grants for "replication"@"%";
+-------------------------------------------------------------------------+
| Grants for replication@%                                                |
+-------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%' |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)


#查询master binlog 节点状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 54782009 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |  54785632 |
+------------------+-----------+
1 row in set (0.00 sec)


#查询server_id
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

#刷新权限：
FLUSH PRIVILEGES;
(flush privileges;)


#忘记root密码
vim /etc/my.cnf
[mysqld]
skip-grant-tables

#MySQL5.7 日志时间参数 log_timestamps
参数全局有效，可设置值为：UTC 和 SYSTEM，默认使用 UTC。UTC 时区 比中国慢 8 个小时，影响查看日志，修改为 SYSTEM 解决问题。
支持动态编辑设置，不过建议写入`my.cnf`配置文件，以免重启之后造成不必要的麻烦。

SET GLOBAL log_timestamps = SYSTEM;
或
/etc/my.cnf
log_timestamps = SYSTEM


#导入 .sql 文件内的数据：
SOURCE filename.sql
mysql -uxxx -p < filename.sql

#查询binlog
mysqlbinlog --no-defaults --start-datetime='2016-11-28 13:15:00' --stop-datetime='2016-11-28 13:16:01' -d iuu_shop mysql-bin.000007

原文链接