05 MySQL DBA日常操作


原文链接: 05 MySQL DBA日常操作

使用mysqld_mutil start命令启动多实例3306、3307,使用mysqld_multi report命令显示结果。

规划多实例目录

mkdir -p /u01/mysql             # 程序目录
mkdir -p /u01/conf              # 配置文件
mkdir -p /u01/data/3306
mkdir -p /u01/data/3307
mkdir -p /u01/log/3306/iblog
mkdir -p /u01/log/3307/iblog
mkdir -p /u01/log/3306/binlog
mkdir -p /u01/log/3307/binlog
mkdir -p /u01/run/3306
mkdir -p /u01/run/3307
mkdir -p /u01/tmp/3306
mkdir -p /u01/tmp/3307

chown -R mysql:mysql /u01
chmod -R 755 /u01

重新编译安装mysql

cd /u01/mysql-5.6.35
rm -rf CMakeCache.txt

cmake \
-DCMAKE_INSTALL_PREFIX=/u01/mysql \
-DINSTALL_DATADIR=/u01/data/3306  \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=yes \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/u01/run/3306/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DSYSCONFDIR=/etc \
-DWITH_READLINE=on

make && make install

配置多实例独自参数文件

cd /u01/conf
vi my3306.cnf
----------------------------------------------------------
[client]
port=3306
socket=/u01/run/3306/mysql.sock

[mysql]
pid_file=/u01/run/3306/mysqld.pid

[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true

# system
basedir=/u01/mysql
datadir=/u01/data/3306
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/u01/run/3306/mysqld.pid
port=3306
server_id=3306
skip_name_resolve=ON
socket=/u01/run/3306/mysql.sock
tmpdir=/u01/tmp/3306

#binlog
log_bin=/u01/log/3306/binlog/binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#logging
log_error=/u01/log/3306/error.log
slow_query_log_file=/u01/log/3306/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/u01/log/3306/relaylog
relay_log_index=/u01/log/3306/relay.index
relay_log_info_file=/u01/log/3306/relay-log.info

#slave
slave_load_tmpdir=/u01/tmp/3306
slave_skip_errors=OFF

#innodb
innodb_data_home_dir=/u01/log/3306/iblog
innodb_log_group_home_dir=/u01/log/3306/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

[mysqld_safe]
datadir=/u01/data/3306
----------------------------------------------------------

# 将上面内容中3306替换成3307,server编辑保存my3307.cnf
vi my3307.cnf

初始化多实例数据库

su - mysql

# 修改环境变量
vi /home/mysql/.bash_profile
----------------------------------------------------------
PATH=$PATH:$HOME/bin:/u01/mysql/bin
----------------------------------------------------------

# 初始化数据库实例
cd /u01/mysql/scripts
./mysql_install_db --defaults-file=/u01/conf/my3306.cnf \
--datadir=/u01/data/3306 --basedir=/u01/mysql --user=mysql

./mysql_install_db --defaults-file=/u01/conf/my3307.cnf \
--datadir=/u01/data/3307 --basedir=/u01/mysql --user=mysql

# 使用独立参数启动实例
mysqld_safe --defaults-file=/u01/conf/my3306.cnf &
mysqld_safe --defaults-file=/u01/conf/my3307.cnf &

# 登陆3306实例
mysql --socket=/u01/run/3306/mysql.sock

show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

# 登陆3307实例
mysql --socket=/u01/run/3307/mysql.sock

show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

# 关闭实例
mysqladmin --socket=/u01/run/3306/mysql.sock shutdown &
mysqladmin --socket=/u01/run/3307/mysql.sock shutdown &

修改/etc/my.cnf参数

# root用户下操作
vi /etc/my.cnf
----------------------------------------------------------
[mysqld_multi]
    mysqld=/u01/mysql/bin/mysqld_safe
    mysqladmin=/u01/mysql/bin/mysqladmin
    user=root
    log=/u01/log/multi.log

[mysqld3306]
    port=3306
    basedir=/u01/mysql
    datadir=/u01/data/3306
    socket=/u01/run/3306/mysql.sock
    server-id=3306
    log_bin=/u01/log/3306/binlog/binlog
    slow_query_log_file=/u01/log/3306/slow.log
    innodb_data_home_dir=/u01/log/3306/iblog
    innodb_log_group_home_dir=/u01/log/3306/iblog
    log_error=/u01/log/3306/error.log
    pid_file=/u01/run/3306/mysqld.pid
    tmpdir=/u01/tmp/3306
    relay_log=/u01/log/3306/relaylog
    relay_log_index=/u01/log/3306/relay.index
    relay_log_info_file=/u01/log/3306/relay-log.info
    slave_load_tmpdir=/u01/tmp/3306

[mysqld3307]
    port=3307
    basedir=/u01/mysql
    datadir=/u01/data/3307
    socket=/u01/run/3307/mysql.sock
    server-id=3307
    log_bin=/u01/log/3307/binlog/binlog
    slow_query_log_file=/u01/log/3307/slow.log
    innodb_data_home_dir=/u01/log/3307/iblog
    innodb_log_group_home_dir=/u01/log/3307/iblog
    log_error=/u01/log/3307/error.log
    pid_file=/u01/run/3307/mysqld.pid
    tmpdir=/u01/tmp/3307
    relay_log=/u01/log/3307/relaylog
    relay_log_index=/u01/log/3307/relay.index
    relay_log_info_file=/u01/log/3307/relay-log.info
    slave_load_tmpdir=/u01/tmp/3307

[client]
    port=3306
    socket =/u01/run/3306/mysql.sock

[mysqld]
    autocommit=1
    general_log=off
    explicit_defaults_for_timestamp=true
    max_allowed_packet=1g
    max_connections=3000
    max_user_connections=2800
    open_files_limit=65535
    skip_name_resolve=ON

    #binlog
    binlog_cache_size=32768
    binlog_format=row
    expire_logs_days=7
    log_slave_updates=ON
    max_binlog_cache_size=2147483648
    max_binlog_size=524288000
    sync_binlog=100

    log_queries_not_using_indexes=0
    slow_query_log=1
    log_slave_updates=ON
    log_slow_admin_statements=1
    long_query_time=1

    slave_skip_errors=OFF

    innodb_adaptive_flushing=ON
    innodb_adaptive_hash_index=ON
    innodb_autoinc_lock_mode=1
    innodb_buffer_pool_instances=8

    #default
    innodb_change_buffering=inserts
    innodb_checksums=ON
    innodb_buffer_pool_size= 128M
    innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
    innodb_doublewrite=ON
    innodb_file_format=Barracuda
    innodb_file_per_table=ON
    innodb_flush_log_at_trx_commit=1
    innodb_flush_method=O_DIRECT
    innodb_io_capacity=1000
    innodb_lock_wait_timeout=10
    innodb_log_buffer_size=67108864
    innodb_log_file_size=1048576000
    innodb_log_files_in_group=4
    innodb_max_dirty_pages_pct=60
    innodb_open_files=60000
    innodb_purge_threads=1
    innodb_read_io_threads=4
    innodb_stats_on_metadata=OFF
    innodb_support_xa=ON
    innodb_use_native_aio=OFF
    innodb_write_io_threads=10
----------------------------------------------------------

mysqld_multi启动/关闭多实例,查看多实例运行状态

# 启动多实例数据库
mysqld_multi start             # 启动全部配置的多实例
mysqld_multi start 3306        # 启动指定server-id的实例
mysqld_multi start 3306-3307   # 启动指定server-id连续的实例

# 查看多实例运行状态
mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

# 关闭多实例数据库
mysqld_multi stop
mysqld_multi stop 3306
mysqld_multi stop 3306-3307

在线迁移MySQL 3306实例上的数据库jfedu到MySQL 3307上。

登陆3306实例,创建jfedu数据库

# 登陆3306实例
mysql -S /u01/run/3306/mysql.sock

# 查看port和server_id
show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3306  |
+---------------+-------+

# 创建jfedu数据库,创建表t1并插入数据
create database jfedu;
use jfedu;
create table t1(id int,name varchar(10));
insert into t1 values(1,'AAAAA');
commit;

在3306实例上创建一个复制帐号

grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl4Slave';

备份jfedu数据库

mysqldump --single-transaction --master-data=2 -uroot jfedu > /tmp/jfedu.sql
# --single-transaction 不锁表
# --master-data=2 在备份文件中记录master_log_file和master_log_pos的值

# 设置主从复制时会用到master_log_file和master_log_pos的值
cat /tmp/jfedu.sql | grep MASTER_LOG_FILE
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000022', MASTER_LOG_POS=750;

在3307实例上恢复jfedu数据库

# 登陆3306实例
mysql -S /u01/run/3307/mysql.sock

# 查看port和server_id
show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3307  |
+---------------+-------+

# 在3307上创建jfedu数据库
create database jfedu;

# 恢复数据库
use jfedu;
source /tmp/jfedu.sql;

3306实例在恢复时有数据插入

use jfedu
insert into t1 values(2,'BBBBB');
commit;

在3307实例上执行change master设置主从复制,并启动复制

# 设置主从复制
change master to
master_host='127.0.0.1',
master_port=3306,
master_user='repl',
master_password='repl4Slave',
master_log_file='binlog.000022',
master_log_pos=750;

# 启动复制
start slave;

# 查看复制状态
show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000022
          Read_Master_Log_Pos: 949
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 479
        Relay_Master_Log_File: binlog.000022
             Slave_IO_Running: Yes               # Yes表示复制正常
            Slave_SQL_Running: Yes               # Yes表示复制正常
......

# 观察3307上数据是否复制过来
select * from jfedu.t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | AAAAA |
|    2 | BBBBB |
+------+-------+

3306实例设置成read only

show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

set global read_only=1;

数据全部一致后,停止复制

stop slave;

MySQL5.6升级到MySQL5.7,正常登录到MySQL5.7,详细步骤。

下载MySQL5.7

下载地址:https://dev.mysql.com/downloads/mysql
mysql5.7下载

上传并解压软件包

[mysql@mysql ~]$ cd /u01
#选择下载的软件包上传
[mysql@mysql u01]$ rz
[mysql@mysql u01]$ ll
total 698668
drwxr-xr-x   2 mysql mysql      4096 Feb 24 11:16 conf
drwxr-xr-x   4 mysql mysql      4096 Feb 24 09:45 data
drwxr-xr-x   4 mysql mysql      4096 Feb 24 11:00 log
drwxr-xr-x  13 mysql mysql      4096 Feb 24 10:16 mysql
drwxr-xr-x  35 mysql mysql      4096 Feb 24 10:05 mysql-5.6.35
-rwxr-xr-x.  1 mysql mysql  32167628 Jan 17 11:16 mysql-5.6.35.tar.gz
-rw-r--r--   1 mysql mysql 683233280 Feb 24 16:36 mysql-5.7.17-linux-glibc2.5-x86_64.tar   # 这个就是二进制mysql5.7软件
drwxr-xr-x   4 mysql mysql      4096 Feb 24 09:50 run
drwxr-xr-x   4 mysql mysql      4096 Feb 24 09:45 tmp

tar -xpvf mysql-5.7.17-linux-glibc2.5-x86_64.tar
tar -xpvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

# 因为是二进制包,解压后就可以用了,文件夹名改短些
mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql5.7

# 修改环境变量
vi /home/mysql/.bash_profile
PATH=$PATH:$HOME/bin:/u01/mysql5.7/bin

查看mysql版本信息

mysql -V
--------------
mysql  Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper
--------------

mysqld_multi start 3306
mysql -S /u01/run/3306/mysql.sock

status;
--------------
mysql  Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:          7
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.35-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /u01/run/3306/mysql.sock
Uptime:                 3 min 57 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.067
--------------

升级数据库字典

mysql_upgrade -S /u01/run/3306/mysql.sock
--------------
Checking if update is needed.
Checking server version.
Error: Server version (5.6.35-log) does not match with the version of
the server (5.7.17) with which this program was built/distributed. You can
use --skip-version-check to skip this check.
--------------

mysql_upgrade -S /u01/run/3306/mysql.sock --skip-version-check
--------------
Checking if update is needed.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1726: Storage engine 'InnoDB' does not support system tables. [mysql.plugin]
--------------

# 重启下数据库
mysqld_multi stop
mysqld --defaults-file=/u01/conf/my3306.cnf &
mysql_upgrade -S /u01/run/3306/mysql.sock --skip-version-check
--------------
Checking if update is needed.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
......
Repairing tables
mysql_old.innodb_index_stats
Error    : Unknown error 1146
status   : Operation failed
mysql_old.innodb_table_stats
Error    : Unknown error 1146
status   : Operation failed
mysql_old.slave_master_info
Error    : Unknown error 1146
status   : Operation failed
mysql_old.slave_relay_log_info
Error    : Unknown error 1146
status   : Operation failed
mysql_old.slave_worker_info
Error    : Unknown error 1146
status   : Operation failed
Upgrade process completed successfully.
Checking if update is needed.
--------------

再查看mysql版本信息,升级完成

mysql -S /u01/run/3306/mysql.sock

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.17-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /u01/run/3306/mysql.sock
Uptime:                 6 min 3 sec

Threads: 1  Questions: 3174  Slow queries: 0  Opens: 368  Flush tables: 1  Open tables: 25  Queries per second avg: 8.743
--------------

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+

课堂笔记整理

MySQL启动

# 推荐启动方式
mysqld_safe --defaults-file=/u01/conf/my3306.cnf &

# 编译安装可以使用的启动方式(很少用)
cd /u01/mysql/support-files/
./mysql.server start

# rpm包安装可以使用的启动方式
/etc/init.d/mysqld start
service mysqld start

# mysqld的启动方式
mysqld --defaults-file=/u01/conf/my3306.cnf &

# 多实例启动方式,需要先修改my.cnf参数,并放到/etc目录下(建议单个实例维护)
mysqld_mutil start

# 查看mysql启动时参数my.cnf查找顺序
mysqld --verbose --help | grep my.cnf

# mysqld_safe方式启动mysql后相关进程,mysqld_safe进程有监控mysqld进程的作用,mysqld异常终止时,mysqld_safe会重启mysqld
ps -ef | grep mysql | grep -v bash | grep -v grep | grep -v su | grep -v ps
mysql     5503 18586  0 16:33 pts/1    00:00:00 /bin/sh /u01/mysql/bin/mysqld_safe --defaults-file=/u01/conf/my3306.cnf
mysql     6342  5503  0 16:33 pts/1    00:00:00 /u01/mysql/bin/mysqld --defaults-file=/u01/conf/my3306.cnf --basedir=/u01/mysql --datadir=/u01/data/3306 --plugin-dir=/u01/mysql/lib/plugin --log-error=/u01/log/3306/error.log --open-files-limit=65535 --pid-file=/u01/run/3306/mysqld.pid --socket=/u01/run/3306/mysql.sock --port=3306

# mysqld方式启动mysql后相关进程
ps -ef | grep mysql | grep -v bash | grep -v grep | grep -v su | grep -v ps
mysql     6401 18586  2 16:36 pts/1    00:00:00 mysqld --defaults-file=/u01/conf/my3306.cnf

MySQL关闭

# 使用mysqld_safe和mysqld启动的关闭方式(推荐的方式)
mysqladmin shutdown
mysqladmin --socket=/u01/run/3306/mysql.sock shutdown &

# 编译安装可以使用的关闭方式
cd /u01/mysql/support-files/
./mysql.server stop

# rpm安装可以使用的关闭方式
/etc/init.d/mysqld stop
service mysqld stop

# 多实例关闭方式
mysqld_mutil stop

# kill进程
kill -9 pid

MySQL登陆

# 本地登陆
mysql
mysql -u$username -p$password

# 远程登陆
mysql -u$username -p$password -h$ip

# 多实例
mysql -u$username -p$password -P$port
mysql --socket=/u01/run/3306/mysql.sock --port=3306

帐户权限设置-创建/删除用户

select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |     # 密码都为空,不安全
| root | mysql     |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | mysql     |          |
+------+-----------+----------+

# insert方式创建用户(用insert方式创建的用户,需要刷新缓存)
insert into mysql.user(user,host,password) values('mytt','127.0.0.1',password(123456));
flush privileges;

select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost |                                           |
| root | mysql     |                                           |
| root | 127.0.0.1 |                                           |
| root | ::1       |                                           |
|      | localhost |                                           |
|      | mysql     |                                           |
| mytt | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+

# mytt登陆mysql命令如下
mysql -umytt -p123456 -h127.0.0.1

select user();
+----------------+
| user()         |
+----------------+
| mytt@127.0.0.1 |
+----------------+

# create方式创建用户
create user lyj@'%' identified by '123456';  # %代表可以从任意位置登陆
mysql -ulyj -p123456 -h127.0.0.1

# 删除用户
drop user lyj;

帐户权限设置-用户授权

# 创建新用户并授权
grant all privileges on *.* to lyj@'%' identified by '123456';

# 查看用户权限
show grants for lyj@'%';
+-------------------------------------------------------------------------------------------------------------+
| Grants for lyj@%                                                                                            |
+-------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyj'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------+

# 给已有用户授权
grant select on *.* to mytt@127.0.0.1;
show grants for mytt@127.0.0.1;
+--------------------------------------------------------------------------------------------------------------+
| Grants for mytt@127.0.0.1                                                                                    |
+--------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'mytt'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------+

帐户权限设置-权限等级

  1. 核心开发权限 select/insert/delete/update
  2. 管理权限--表级 create table/drop table/lock table
  3. 管理权限--server级别 create database/create user等

MySQL数据库安全配置

1.禁用/删除多余的管理员帐号,设置用户密码

# 删除多余的帐号
delete from mysql.user where user !='root' and password='';
flush privileges;

# 使用set方式设置root用户密码
set password for root@localhost = password('123456');
set password for root@127.0.0.1 = password('123456');

# 使用mysqladmin工具设置密码
## 格式: mysqladmin -u用户名 -p旧密码 password 新密码
## 密码为空时,直接输入回车确认
mysqladmin -uroot -p password 654321
Enter password:
Warning: Using a password on the command line interface can be insecure.

## 修改已有密码
mysqladmin -uroot -p654321 password 123456
## 执行后会出现如下警告提示,可以忽略:
## Warning: Using a password on the command line interface can be insecure.
## 翻译过来的意思:在命令行界面上使用密码是不安全的。

# 登陆mysql
mysql -uroot -p123456

# 删除所有密码为空的帐号
delete from mysql.user where password='';
flush privileges;

# 确保所有用户都有密码
select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mytt | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+

2.删除掉db表数据(test权限)

use mysql;
truncate table mysql.db;

3.删除test库

drop database test;

4.修改管理员帐户名
5.密码复杂度要求
6.权限最小化

表操作--线上可以直接删除表吗?

生产环境上,不可以直接删除表,要删除表步骤如下:

# 构建环境
drop database lyj;
create database lyj;
use lyj;
create table t1 (id int, name varchar(10));
insert into t1 values (1,'lyj');

# 1.查看表
show tables;

# 2.检查表是否被访问
show processlist;

# 3.重命名临时表
rename table t1 to t1_bak;

# 4.备份临时表
mysqldump -uroot -p123456 lyj t1_bak > /tmp/lyj_t1_bak_20170223.sql

# 5.一段时间后删除临时表
drop table t1_bak;
show tables from lyj like '%t1%';

常用命令

show databases;
use mysql;
show tables;
select user,host,password from mysql.user;
grant all privilege on *.* to test_1@'%';
mysql -h127.0.0.1 -utest_1
select user();
create database jianfeng;
create table user(id int,name varchar(10));
grant select on jianfeng.user to test_1@'%';
flush privileges;
show master status\G;
change master to xxx;
show engine innodb status\G
show tables from information_schema like 'INNODB%';
mysqld_safe --defaults-file=/u01/my3306/my.cnf &
mysqladmin -S /u01/my3306/run/mysql.sock shutdown &

/u01/mysql/bin/mysql_secure_installation
`