创建用户，数据库，表
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');
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
原文链接
