MySQL日常维护（超级大杂烩）

MySQL 日常维护

其中第一至第六, 每天都需要执行, 第七到第九定期执行(1周或者2周一次？)

一检查数据库运行状态

1 通过putty.exe登录远程数据库服务器,在shell命令行里面执行:

service mysql status

SUCCESS! MySQL running (1302)

则表示数据库正在运行之中

2 检查错误日志

进入/var/lib/mysql,查看错误日志，例如mysql.err。看近期mysql在运行中是否有错误异常发生。

tail -f /var/lib/mysql/mysql.err

比如发现类似错误：

090418 19:59:11 [ERROR] Got error 127 when reading table ‘./csf/ms_company’

对于127以及126的类似错误，可以采取如下解决措施：

(1) 进入mysql窗口执行： Mysql> repair csf.ms_company;

(2) 进入shell命令窗口： Shell> myisamchk /usr/local/mysql/var/csf/ms_company.MYI

【】在线修复的时候，要保证没有程序在访问要修复的表。

3 检查备份日志

检查前一天的备份的日志，看备份是否成功完成，或者备份过程中出

现什么错误提示。

4 检查数据库调度任务events事件

二检查数据库服务器磁盘空间, 记录最近一天磁盘使用量

通过如下shell命令检查：

df -hvT

Filesystem          Type      Size Used Avail Use% Mounted on
devtmpfs            devtmpfs 852M     0 852M   0% /dev
tmpfs               tmpfs     872M     0 872M   0% /dev/shm
tmpfs               tmpfs     872M 8.6M 863M   1% /run
tmpfs               tmpfs     872M     0 872M   0% /sys/fs/cgroup
/dev/mapper/ol-root xfs        14G 3.6G 9.9G 27% /
/dev/sda1           xfs      1014M 174M 841M 18% /boot
tmpfs               tmpfs     175M     0 175M   0% /run/user/0

得出目前已剩余磁盘空间,跟昨天的记录一对比,得出每天磁盘使用量为3G，如果磁盘可用空间小,就需要清理旧的binlog.

三检查表

打开MySQL Administration工具，登录数据库服务器，检查每个库的表情况，

遇到有坏表的，直接Repair Table修复掉。

四检查master服务器的master服务以及slave机的slave服务状态

查看master服务器状态：行正常的状态如下所示:

mysql -u root -p

show master status;

+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
| mysql-bin.000023 | 231 | | | 08a5acd3-6673-11e8-b993-000c29ee89cd:1-4,
1fdb51e5-6242-11e8-9e40-000c293b8095:1-29808 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看mysql用户线程状态：运行正常的状态如下所示:

show processlist;

查看slave服务器状态：运行正常的状态如下所示:

show slave status \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000023
          Read_Master_Log_Pos: 231
               Relay_Log_File: mysqlstandby-relay-bin.000008
                Relay_Log_Pos: 361
        Relay_Master_Log_File: mysql-bin.000023
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 231
              Relay_Log_Space: 541
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 1fdb51e5-6242-11e8-9e40-000c293b8095
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 08a5acd3-6673-11e8-b993-000c29ee89cd:1-4,
1fdb51e5-6242-11e8-9e40-000c293b8095:1-29808
                Auto_Position: 1
1 row in set (0.00 sec)

五检查Excel错误

详细见文档“excel_error_check.sql”。

六， MySQL用户账户管理

对用户严格管理，单用户只能赋予执行单个业务的权限，防止对数据的误操作。示范命令如下：

创建用户：

grant select,insert,update,delete on sbtest.* to 'test'@'192.168.1.10' identified by 'jdy62ZHA';

查看用户:

select * from mysql.user;

select * from mysql.db;

select * from mysql.tables_priv;

select * from mysql.columns_priv;

select * from mysql.procs_priv;

赋予权限

grant update on sbtest.* to 'test'@'192.168.1.10';

grant update(id) on sbtest.sbtest to 'test'@'192.168.1.10';

收回权限

revoke drop on *.* from 'test'@'192.168.1.10';

删除过期用户

drop user 'test'@'192.168.1.10';

七，定期索引检查、索引优化

重点检查大表比如rpt_style1_0到rpt_style1_f等的索引。

八，定期检查表结构以及数据

通过 Administration工具检查，将主机与从机的库结构以及数据进行

全方位的对比对比。比如说,表以及数据量的总记录,索引的记录数,触发器的记录数,存储函数存储过程的记录数等.

1 检查和修复MyISAM表的一个方式是使用CHECK TABLE和REPAIR TABLE语句。

2 优化MyISAM表，对于没有问题的表执行optimize table进行做碎片整理，回收空闲空间，例如:

optimize table sbtest.sbtest;

或者是，如果可以停一会mysqld服务器，进入数据库目录，当mysqld停止时候使用该命令：

service mysql stop

cd /var/lib/mysql

myisamchk -r -s -sort-index -o --myisam_sort_buffer_size=16M */*.MYI

myisamchk: error: 140 when opening MyISAM-table 'mysql/columns_priv.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/db.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/event.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/func.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/help_category.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/help_keyword.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/help_relation.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/help_topic.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/ndb_binlog_index.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/plugin.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/proc.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/procs_priv.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/proxies_priv.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/servers.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/tables_priv.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_leap_second.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_name.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_transition.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_transition_type.MYI'
myisamchk: error: 140 when opening MyISAM-table 'mysql/user.MYI'

3 修复innodb表

mysql目前没有修复innodb 表的工具，只能用innodb_force_recovery=1，避免在导出数据时再crash。在my.cnf中设置好后重启库，再用 mysqldump或者select *把出问题的表导出来。然后重新导入（删除原表）。

九，检查备份文件，将从机的备份数据恢复到测试库，检验备份数据是否正确。

具体操作参见文档“MySQL备份恢复初稿.docx”

MySQL运维之--日常维护操作

一、Linux内核和发行版本

uname -a

Linux mysql 4.1.12-94.3.9.el7uek.x86_64 #2 SMP Fri Jul 14 20:09:40 PDT 2017 x86_64 x86_64 x86_64 GNU/Linux

cat /etc/issue

\S
Kernel \r on an \m

二、glibc的版本
/lib/libc.so.6 ---没有man函数据的动态链接库

三、MySQL的版本
MySQL二进制分发版的文件名格式为：mysql-VERSION-OS.tar.gz
例如：Linux-Generic(glibc 2.5) (x86,64bit),Compressed TAR Archive(mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz)

1、下载
https://dev.mysql.com/downloads/mysql/

2、查rpm包装在什么目录下

rpm -qpl MySQL-server-5.6.40-1.el7.x86_64.rpm |more

warning: MySQL-server-5.6.40-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
/etc/init.d/mysql
/etc/logrotate.d/mysql
/etc/my.cnf
/etc/my.cnf.d
/usr/bin/innochecksum
/usr/bin/my_print_defaults
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_convert_table_format
/usr/bin/mysql_fix_extensions
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_setpermission
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysql_zap
/usr/bin/mysqlbug
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqldumpslow
/usr/bin/mysqlhotcopy
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/auth.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/auth_test_plugin.so
/usr/lib64/mysql/plugin/connection_control.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/debug/adt_null.so
/usr/lib64/mysql/plugin/debug/auth.so
/usr/lib64/mysql/plugin/debug/auth_socket.so
/usr/lib64/mysql/plugin/debug/auth_test_plugin.so
/usr/lib64/mysql/plugin/debug/connection_control.so
/usr/lib64/mysql/plugin/debug/innodb_engine.so

rpm -qpl MySQL-client-5.6.40-1.el7.x86_64.rpm |more

warning: MySQL-client-5.6.40-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
/usr/bin/msql2mysql
/usr/bin/mysql
/usr/bin/mysql_config_editor
/usr/bin/mysql_find_rows
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqlaccess.conf
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog
/usr/bin/mysqlcheck
/usr/bin/mysqldump
/usr/bin/mysqlimport
/usr/bin/mysqlshow
/usr/bin/mysqlslap
/usr/share/doc/MySQL-client-5.6.40
/usr/share/doc/MySQL-client-5.6.40/COPYING
/usr/share/doc/MySQL-client-5.6.40/README
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/mysql.1.gz
/usr/share/man/man1/mysql_config_editor.1.gz
/usr/share/man/man1/mysql_find_rows.1.gz
/usr/share/man/man1/mysql_waitpid.1.gz
/usr/share/man/man1/mysqlaccess.1.gz
/usr/share/man/man1/mysqladmin.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysqlcheck.1.gz
/usr/share/man/man1/mysqldump.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysqlshow.1.gz
/usr/share/man/man1/mysqlslap.1.gz

3、更改rpm安装路径

rpm --help

rpm --prefix --relocate

rpmbuild spec binary rpm

yum install

四、rpm安装

rpm -ivh xxx.rpm

rpm -pql xxx.rpm

MySQL 实例安装和启动

1. 安装

mysql_install_db --defaults-file=/root/data/mysql3306/my.cnf --basedir=/usr/ --datadir=/root/data/mysql3306/data

2. 启动

mysqld_safe --defaults-file=/root/data/mysql3306/my.cnf &

3. 登录

mysql -h 127.0.0.1 -u root -P 3306 -p

五、安装演示：

1.关闭mysql

ps -ef|grep mysqld

kill 3397 3801

2.安装

cat init3306.sh

sh init3306.sh

3.启动

cat start3306.sh

sh start3306.sh

注意：mysql_install_db(通过安装rpm包产生mysql_install_db)，有如下命令查看：

rpm -pql MySQL-server-5.6.40-1.el7.x86_64.rpm |grep install

warning: MySQL-server-5.6.40-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
/usr/bin/mysql_install_db
/usr/bin/mysql_secure_installation
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz

which mysql_install_db

/usr/bin/mysql_install_db

4.看日志

tail -100f /var/lib/mysql/mysql.err

发生数据字典不存在，就会自动创建。。。。

5.查看进程

ps -ef|grep mysqld

root      1948     1 0 19:17 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid
mysql     2129 1948 0 19:17 pts/0    00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=mysql.err --pid-file=/var/lib/mysql/mysql.pid
root      2328 1539 0 22:12 pts/0    00:00:00 grep --color=auto mysqld

mysqld_safe是mysqld的父进程

6.登录

sh my3306.sh --mysql -h 127.0.0.1 -u root -P 3306

六、脚本：

1.---安装mysql:

vi init3306.sh

rm -rf /root/data/mysql3306/data/*
rm -rf /root/log/mysql3306/iblog/*
rm -rf /root/log/mysql3306/binlog/*
chmod -R 777 /root/data/mysql3306/data/
chmod -R 777 /root/log/mysql3306/iblog/
chmod -R 777 /root/log/mysql3306/binlog/

chmod 755 /root/data/mysql3306/my.cnf

mysql_install_db --defaults-file=/root/data/mysql3306/my.cnf --basedir=/usr/ --datadir=/root/data/mysql3306/data

chmod -R 777 /root/data/mysql/3306/data/
chmod -R 777 /root/log/mysql3306/iblog/
chmod -R 777 /root/log/mysql3306/binlog/

2.--启动mysql:

vi start336.sh

mysqld_safe --defaults-file=/root/data/mysql3306/my.cnf &

3.--登录mysql:

vi my3306.sh

mysql -h127.0.0.1 -uroot -P3306 -p

------研究下

cd /root/data/mysql3306/data/mysql

cd /root/data/mysql3306/data/performance_schema --性能相关的

cd /root/data/mysql3306/data/test ---测试库

cd /root/log/mysql3306/iblog/ ---innodb自己的数据和日志

------了解my.cnf(多实例用端口来取分)

vi /root/data/mysql3306/my.cnf

[client]
port=3306
socket=/root/data/mysql3306/run/mysql.sock

[mysql]
port=3306
promprt=\\u@\\d \\r:\\m:\\s>

[mysqld]
default-storage-engine=INNODB
character-set-server=iatin1
explicit_defaults_for_timestamp=true

#dir
innodb_log_group_home_dir=/root/log/mysql3306/iblog
innodb_data_home_dir=/root/log/mysql3306/iblog
basedir=/usr
datadir=/root/data/mysql3306/data
tmpdir=/root/data/mysql3306/tmp
slave_load_tmpdir=/root/data/mysql3306/tmp
log-error=/root/data/mysql3306/log/alert.log
slow_query_log_file=/root/data/mysql3306/log/slow.log
relay_log_info_file=/root/log/mysql3306/binlog/relay-log.info
master-info-file=/root/log/mysql3306/binlog/master.info
socket=/root/data/mysql3306/run/mysql.sock
log-bin=/root/log/mysql3306/binlog/binlog
relay-log=/root/log/mysql3306/binlog/relaylog

innodb_force_recovery=0

七、操作mysql

1、登录mysql:

本地：
mysql -u root -p

远程：
mysql -u root -p -h 192.168.1.12

多实例：
mysql -u root -p -P 3306

2、用户操用

(1)创建用户

create user 'test2'@'192.168.1.10' identified by 'test2';

(2)用户授权

单纯的授权

grant all privileges on *.* to 'test2'@'localhost';

grant insert,update,delete,select on *.* to 'test2'@'localhost';

授权并创建用户

--创建用户并刷缓存

grant all privileges on *.* to 'test2'@'localhost' identified by 'test2';

--对象权限

grant all privileges on *.* to 'test2'@'localhost';

--系统权限 (super相当于oracle中的dba权限)

grant super on *.* to 'test2'@'localhost';

3、实操

--查看所有的数据库
show databases;

--切到mysql数据库
use mysql;

----查mysql的所有用户，这个是由mysql_install_db创建的
select user,host,password from mysql.user;

--all代表(select update,delete,alter admin,super_acl),第一个*代表所有数据库，第二个*代表所有对象，%代表所有的客户端都可以访问。
grant all privileges on *.* to 'test2'@'%';

----用grant创建的用户登录mysql
mysql -h 127.0.0.1 -u test2 -p

---当前是什么用户
select user();

---创建数据库（mysql中的数据库类似于oracle中的schema
create database test2;

---创建表
create table test2(id int) engine=innodb;

---sbtest.test2表的查询授权给test2用户
grant select on sbtest.test2 to 'test2'@'%';

---把mysql.user表的用户权限重新刷到内存中
flush privileges;

show master status \G

---查看当前用户的连接,线程形式（类似oracle中的v$session）
show processlist;

4、drop table处理

(可以快速切回来rename table test3 to test2;)
rename table test2 to test3;

备份mysqldump：
mysqldump -h 127.0.0.1 -u root -p sbtest test > /home/sbtest.test.sql

drop table sbtest;

5、自增主键(最好是自己定义主键，系统默认的是全局的增量)
create table test (id int primary key auto_increment,name varchar(100)) engine=innodb;

show create table test \G

create index test_name_idx on test(name);

show create table test \G

insert into test(name) values('test');

select * from test;

6、alter table处理 --会动原来的数据，需要拷贝数据

alter table test add coll int;

7、执行计划

select * from test where id=1 \G

explain select * from test where id=1;

create index test_id_coll_idx on test(id,coll);

explain select * from test where id=1;

create index test_col_name on test(coll,name);

explain select * from test where coll>10 and name='xx';

show create table test \G

alter table test drop index test_name_idx;

explain select * from test where coll>10 and name>'xx';

8、数据导出

（1）用dump导出数据

mysqldump -h 127.0.0.1 -u root -p sbtest test > /home/sbtest.test.sql

mysql -u root -p

drop table sbtest.test;

use sbtest;

--导入数据
source /home/sbtest.test.sql

(2)用select导出数据

select * from test into outfile '/home/select_export_sbtest.test.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

9、数据迁移

(1)停机方式

mysqldump/loadata

(2)不停机方式

物理上：搭备库（可以级联5.5-->5.6，向下兼容的）
把主库read only,备库就能把主库转过来的binlog消化完，再把备库切为主

show global variables like '%read_only%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | OFF   |
| tx_read_only     | OFF   |
+------------------+-------+
3 rows in set (0.00 sec)

set global read_only=on;

--插不进的，不能用root用户
insert into test(name) values('xx');

(3)不同平台小表：oracle--->mysql

脚本：synfull.pl

(4)不同平台的一个大表迁多：增量迁移

a.把数据的全量迁过去

b.把迁的过程中产生的日志传过去

c.apply增量

d.锁表切切换

（5）增量

a.Oracle:物化视图

b.MySQL:trigger

10、binlog

reset master; --会把当前的binlog清掉

show binlog events;

类似于：
mysqlbinlog -vvv /var/lib/mysql/mysql-bin.000024 > /home/mysql-bin.000024.txt

less /home/mysql-bin.000024.txt

WAL: write ahead log,日志优先写

11、归档

flush logs;

show master status;

write ahead log. recover backup, duriably. undo acid mvcc

12、参数和统计信息

----参数
show variables;

show variables like '%bin%';

----统计信息
show status;

show global status like '%insert%';

show variables like '%default%';

---不影响当前会话的操作，影响新建立的连接
set global default_storage_engine=myisam;

---影响当前会话的操作
set session default_storage_engine=myisam;

---连接池

show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout     | 50       |
| innodb_rollback_on_timeout   | OFF      |
| interactive_timeout          | 28800    |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 3600     |
| wait_timeout                 | 28800    |
+------------------------------+----------+
13 rows in set (0.00 sec)

MySQL数据库的日常使用及维护

一：使用MySQL数据库：

1、查看数据库结构：

查看当前服务器中有哪些库：

SHOW DATABASES 语句：用于列出当前MySQL服务器中包含的库；经过初始化后的MySQL服务器，默认建立了三个库：information_schema、mysql和performance_schema（其中mysql库中包含了用户认证相关的表）

查看当前使用的库中有哪些表：

SHOW TABLES 语句：用于列出当前所在的库中包含的表。操作之前，需要先使用USE语句切换到所使用的库：

use mysql;

show tables;

Mysql数据库的数据文件默认存放在/var/lib/mysql目录下，每个数据库对应一个子目录，用于存储数据表文件；

每一个数据表对应为三个文件，后缀名分别为”.frm”” .MYD ”” .MYI ”。

a.查看表的结构：

DESCRIBE 语句：用于显示表的结构，即组成表的各字段（列）的信息；

需要指定“库名.表名”作为参数；

若只指定表名参数，则需先通过“USE”语句切换到目标库。

创建及删除库和表：

b、创建新的库：

CREATE DATABASE 语句：用于创建一个新的库，需指定数据库名称作为参数。

Eg：创建一个名为welcome的库：

CREATE DATABASE welcome;

新建的空数据库在/var/lib/mysql目录下会自动生成一个与新建的库名相同的空文件夹。

c、创建新的表：

CREATE TABLE 语句：用于在当前库中创建新的表，需指定数据表名称作为参数，并定义该表格所使用的各字段。

格式： CREATE TABLE 表名（字段1名称类型，字段2名称类型，… ，PRIMARY kEY （主键名））

在welcome库中创建users表。其中，字段定义部分的DEFAULT用于设置默认的密码字串，PRIMARY用于设置主键字段名。

mysql> USE welcome;

mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

删除一个数据表：

DROP TABLE 语句：用于删除库中的表，需要指定“库名.表名”作为参数；若只指定表名参数，则需先通过“USE”语句切换到目标库。

Eg：删除welcome库中的users表：

mysql> DROP TABLE welcome.users;

删除一个数据库：

DROP DATABASE 语句：用于删除指定的库，需要指定库名作为参数。

Eg：删除名为welcome的库：

mysql> DROP DATABASE welcome;

3、管理表中的数据记录：

插入数据记录：

INSERT INTO 语句：用于向表中插入新的数据记录。

格式： INSERT INTO 表名（字段1，字段2，…） VALUES（字段1的值，字段2的值，…）

向welcome库中的users表中插入一条记录：用户www，对应的密码为“123456”：

mysql> use welcome;

mysql> INSERT INTO users(user_name,user_passwd) VALUES('www',PASSWORD('123456'));

Eg：向welcome库中的users表中插入一条新的记录，用户qqq，对应密码“654321”。

mysql> use welcome;

mysql> INSERT INTO users VALUES('qqq',PASSWORD(654321));

查询数据记录：

SELECT 语句：用于从指定的表中查找符合条件的数据记录。

格式： SELECT 字段名1，字段名2，… FROM 表名 WHERE 条件表达式

表示所有字段可以使用通配符“*”，若要列出所有的数据记录则可以省略WHERE条件子句。

Eg：查看welcome库中的users表内的所有数据记录：

mysql> select * from welcome.users;

根据特定的条件查找记录，WHERE条件子句是必不可少的。

Eg：查找users表中用户名为qqq的记录，输出其用户名、密码字段的信息：

mysql> SELECT user_name,user_passwd from welcome.users where user_name='qqq';

修改数据记录：

UPDATE 语句：用于修改、更新表中的数据记录。

UPDATE 表名 SET 字段名1=字段值1[，字段名2=字段值2] WHERE 条件表达式

Eg：修改users表中用户名为www的记录，将密码字串设为空值，并验证：

mysql> UPDATE welcome.users SET user_passwd=PASSWORD('') WHERE user_name='www';

mysql> select * from welcome.users;

mysql数据库服务器中，用于访问数据库的各种用户信息都保存在mysql库的user表中。

删除数据记录：

DELETE 语句：用于删除表中指定的数据记录。

格式： DELETE FROM 表名 WHERE 条件表达式

Eg：删除users表名为www的数据记录，并验证：

mysql> DELETE FROM welcome.users WHERE user_name='www';

mysql> SELECT * FROM welcome.users;

在mysql数据库服务器中，默认添加了从本机访问数据库的空用户（user、password均为空）。基于数据库安全性考虑，应该删除这些空用户。

mysql> SELECT user,host,password FROM mysql.user WHERE user='';

mysql> DELETE FROM mysql.user WHERE user='';

推荐阅读：

Percona_Toolkit 简介及安装 -- MySQL管理员必备的工具包 http://www.linuxidc.com/Linux/2013-06/86631.htm

二：维护MySQL数据库：

1、数据库的用户授权：

Mysql数据库的root用户账号拥有对其所有库、表的全部权限，频繁使用root账号会给数据库服务器带来安全风险。所以实际工作中需要建立一些低权限的用户：

授予权限：

GRANT 语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户，否则用于修改已有用户信息。

GRANT 权限列表 ON 库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码‘]

使用GRANT语句的注意事项：

权限列表：用于列出授权使用的各种数据库操作，以逗号分隔。All表示所有权限；

库名.表名：用于指定授权操作的库和表的名称，其中可以使用“*”。Eg：使用“welcome.*”表示授权操作的对象为welcome库中的所有表。

用户名@来源地址：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源的地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址。Eg：“%.benet.com”、“192.168.10.%”等

IDENTIFIED BY：设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略则用户的密码将为空。

使用GRANT语句授权的用户记录，会保存到mysql库的user、db、host、tables_priv等。

Eg：添加一个名为zqq的数据库用户，并允许其从本机访问，对welcome库中的所有表具有查询权限，验证密码为“123456”。

mysql> GRANT select ON welcome.* TO 'zqq'@'localhost' IDENTIFIED BY '123456';

Eg：以用户zqq的身份连接到数据库，当执行授权的数据库操作时将被允许，而执行非授权的数据库操作时将被拒绝：

在企业服务器的应用中，数据库与网站服务器有时候是相互独立的。因此在mysql服务器中，应根据实际情况创建新的用户授权，允许授权用户从网站服务器访问数据库；

通常的做法是：创建一个或几个网站专用的库，并授权所有权限，限制访问的来源IP地址。

Eg：新建bash库，并授权从IP地址为192.168.10.1的主机连接，用户名为zqq，密码123456，允许在bash库中执行所有操作。

mysql> CREATE DATABASE bash;

mysql> GRANT all ON bash.* TO 'zqq'@'192.168.10.1' IDENTIFIED BY '123456';

查看权限：

SHOW GRANTS 语句：专门用来查看数据库用户的授权信息，通过FOR语句可指定查看的用户对象（必须与授权时使用的格式： SHOW GRANTS FOR 用户名@来源地址

Eg：查看用户zqq从主机192.168.10.1访问数据库时得授权信息。

mysql> SHOW GRANTS FOR 'zqq'@'192.168.10.1';

撤销权限：

REVOKE 语句：用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到mysql服务器，但将禁止执行对应的数据库操作：

格式：REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

Eg：撤销用户zqq从本机访问数据库bash的所有权限：

mysql> REVOKE all ON bash.* FROM 'zqq'@'localhost'；

mysql> SHOW GRANTS FOR 'zqq'@'localhost';

二、数据库的备份与恢复：

及时备份数据库是信息安全管理的重要内容之一，mysql数据库的备份采用直接打包数据库文件夹/var/lib/mysql或者使用专用的导出工具。

备份数据库：

通过mysqldump命令可以将指定的库、表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用。

A执行导出操作：

格式1：导出指定库中的部分表

Mysqldump [选项] 库名 [表名1][表名2] . .>/备份路径/备份文件名

格式2：导出一个或多个完整的库（包括其中所有的表）

Mysqldump [选项] --databases 库名1[库名2]… >/备份路径/备份文件名

格式3：备份mysql服务器中的所有的库

Mysqldump [选项] --all-databases >/备份路径/备份文件名

其中“ -u ”用来指定数据库的用户名；“ -p ”用来指定数据库的密码

Eg：将mysql库中的user表中导出为/home/mysql.user.sql文件，将整个mysql库导出为/home/mysql.sql文件，所有操作都以root用户的身份验证：

mysqldump -u root -p --set-gtid-purged=OFF mysql user > /home/mysql.user.sql

mysqldump -u root -p --set-gtid-purged=OFF --databases mysql > /home/mysql.sql

若要备份整个mysql服务器的所有库，应使用格式3，当导出的数据量较大时，可以添加‘--opt‘选项以优化执行速度。

Eg：创建备份文件/home/all-databases.sql，其中包括mysql服务器中的所有库：

mysqldump -u root -p --set-gtid-purged=OFF --opt --all-databases > /home/all-databases.sql

B查看备份文件内容：

Eg：过滤出/home/all-databases.sql脚本中的数据库操作语句：

grep -v "^--" /home/all-databases.sql |grep -v "^/" |grep -v "^$" |more

恢复数据库：

格式： mysql [选项] [库名] [表名] < /备份路径/备份文件名

当备份文件中只包含表的备份，而不包括创建库的语句时，则执行导入操作时必须指定库名，且目标库必须存在。

Eg：从备份文件中/home/mysql.user.sql中将表导入到mysql库：

mysql -u root -p mysql < /home/mysql.user.sql

若备份文件中已经包括完整的库信息，则执行导入操作时无需指定库名。

Eg：从备份文件/home/all-databases.sql恢复其中的所有库：

mysql -u root -p < /home/all-databases.sql

日常维护MySQL常用sql

1.慢sql情况查询：

可以使用以下三种方式查询，第一种是了解MySQL进程大概情况；第二种是按照影响时间倒序的，可以查询到目前最慢的一条sql；第三种是防止sql 的info消息过长而无法显示完整。

了解MySQL进程大概情况

show processlist;

按照影响时间倒序的，可以查询到目前最慢的一条sql

select * from information_schema.`PROCESSLIST` where info is not null order by time desc;

防止sql 的info消息过长而无法显示完整

show full processlist;

补充一下，若出现大量慢sql，在不影响业务的前提下，可以将一些select先kill掉，然后来缓冲一下MySQL的性能问题

select concat('kill ',id,' ;') from information_schema.`PROCESSLIST` where info like 'select%' and time>3 order by time desc;

2.连接数的查询：

可以使用以下sql查询到当前实例下所有库的连接数（由于该sql是根据同一个host来判断的，所以可能存在一个ip，不同端口有多个连接）

select db,count(1) from information_schema.`PROCESSLIST` group by db order by 2 desc;

+------+----------+
| db | count(1) |
+------+----------+
| NULL | 3 |
+------+----------+
1 row in set (0.00 sec)

若想查询到完整的host

select db,host from information_schema.`PROCESSLIST`
-> -- where db = dbname
-> ;

查询有哪些host连接到当前实例，而不考虑host端口

select substring_index(host,':',1) ip,count(1) from information_schema.`PROCESSLIST` where db is null group by ip order by 2 desc;

+--------------+----------+
| ip           | count(1) |
+--------------+----------+
| localhost    |        1 |
| mysql3       |        1 |
| mysqlstandby |        1 |
+--------------+----------+
3 rows in set (0.00 sec)

3.磁盘空间的预估：

主要是根据数据量和索引量来对一个实例进行预估磁盘容量，以下sql统计出来的结果都是以MB为单位

查某个数据库的总容量（查数据库mysql的总容量）

select round(sum(data_length+index_length)/1024/1024,2) as total_db_mb from information_schema.tables where table_schema like 'mysql%';

+-------------+
| total_db_mb |
+-------------+
| 0.88 |
+-------------+
1 row in set (0.00 sec)

查磁盘告警的rds的表容量

select table_schema,table_name,table_rows,round((data_length+index_length)/1024/1024,2) as total_mb from information_schema.tables
    -> where -- table_schema in ('dbname') and
    -> table_schema not in ('mysql','information_schema','performance_schema','sys_info')
    -> -- and table_name in ('tbname1','tbname2')
    -> order by total_mb desc;

+--------------+------------+------------+----------+
| table_schema | table_name | table_rows | total_mb |
+--------------+------------+------------+----------+
| sbtest       | test       |          0 |     0.05 |
| xiaoboluo    | test       |          4 |     0.02 |
| sbtest       | test_log   |          0 |     0.02 |
+--------------+------------+------------+----------+
3 rows in set (0.00 sec)

mysql日常维护

1.mysql权限管理

1.1 mysql账户权限信息被存储在mysql数据库的user 、db、host、tables_priv、column_priv和procs_priv表中，mysql启动时服务器将这些数据库表内容读入内存。推荐grant和revoke语句

例如：

grant select,update on mysql.user to 'test'@'192.168.1.10' identified by 'jdy62ZHA';

flush privileges;

创建成功后查看mysql数据库表的变化

select * from mysql.user where user='test' \G

*************************** 1. row ***************************
                  Host: %
                  User: test
              Password:
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
max_user_connections: 0
                plugin: mysql_native_password
authentication_string:
      password_expired: N
*************************** 2. row ***************************
                  Host: 192.168.1.10
                  User: test
              Password: *2FF960917BC9231714223F746ECBC728FCC24E59
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
max_user_connections: 0
                plugin: mysql_native_password
authentication_string:
      password_expired: N

select * from mysql.db where user='test' \G

Empty set (0.01 sec)

select * from mysql.tables_priv where user='test' \G

*************************** 1. row ***************************
       Host: 192.168.1.10
         Db: mysql
       User: test
Table_name: user
    Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Update
Column_priv:
1 row in set (0.00 sec)

mysql权限按照user 、db、tables_priv、column_priv检查顺序，如果对应表权限为Y，则不会检查后面的表

1.2.查看或修改账户权限

例如：

show grants for 'test'@'192.168.1.10' \G

*************************** 1. row ***************************
Grants for test@192.168.1.10: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON *.* TO 'test'@'192.168.1.10' IDENTIFIED BY PASSWORD '*2FF960917BC9231714223F746ECBC728FCC24E59'
*************************** 2. row ***************************
Grants for test@192.168.1.10: GRANT SELECT, UPDATE ON `mysql`.`user` TO 'test'@'192.168.1.10'
2 rows in set (0.00 sec)

1.3 回收账户权限

revoke usage on *.* from 'test2'@'192.168.1.10';

drop user 'test2'@'192.168.1.10';

2.mysql日志管理

mysql服务支持的日志有二进制日志、错误日志、访问日志和满查询日志。

2.1 二进制日志（binlog）

记录所有DDL和DML操作，但不包括数据查询语句，通过mysqlbinlog查看例如：

mysqlbinlog mysql-bin.000001 |cat -n |more

2.1.1 删除binlog，用purge binary logs 例如：

删除指定序号之前的二进制日志

mysql -u root -p

purge binary logs to 'mysql-bin.000005';

删除指定时间之前的二进制日志

mysql -u root -p

purge binary logs before '2018-07-04 18:57:00';

2.1.2 指定参数设置二进制文件保留天数“expire_logs_days=#”

自动清理14天之前的二进制日志文件

set global expire_logs_days=14;

vi /usr/my.cnf

expire_logs_days=14

2.2 操作错误日志

通过设置“--log_error=[file-name]” 指定错误日志存放位置，如没有设置，则错误日志默认位于mysql服务的datadir目录下

vi /usr/my.cnf

log_error='/var/lib/mysql/mysql.err'

2.3 访问日志

记录了所有关于客户端发起的链接，查询和更新语句，由于记录了所有的操作，在相对繁忙的系统中建议关闭

在配置文件“--log=[file-name]” 指定访问日志的位置，另外一种方法可以在登陆mysql实例后通过变量启用此日志，如

show global variables like '%general_log%';

set global general_log=on;

2.4 慢查询日志

记录了时间超过参数long_query_time（单位是秒）所设定值的SQL语句日志，对于审核和开发者发现性能问题及时优化有重要意义。

如需启用该日志可以在配置文件中设置 “slow_query_log" ，没有指定文件名，则默认hostname-slow.log作为文件名，并存放在数据目录中

show global variables like '%long_query_time%';

+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

show global variables like '%slow_query_log%';

开启慢查询日志功能

set global slow_query_log=on;

show global variables like '%slow_query_log%';

vi /usr/my.cnf

slow_query_log=on

慢查询日志分析工具 mysqldumpslow

-s 排序类型

怎样排序这个输出。排序类型的这个值应该从以下清单中选择：

t, at: 按查询时间来排序或者按平均查询时间来排序

l, al: 按锁定时间来排序或者按平均锁定时间来排序

r, ar: 按返回记录数来排序或者按平均返回记录数来排序

c: Sort by count

默认情况下, mysqldumpslow按照平均查询时间来排序(等于-s at)。

[-s] 排序类型，可选al:平均锁定时间 ar:平均返回记录数 at：平均查询时间；

[-t] 只显示指定的行数；

mysqldumpslow -s at /var/lib/mysql/mysql-slow.log

3. mysql备份与恢复

3.1 备份方式可以通过直接备份数据文件或使用mysqldump命令将数据库数据导出到文本文件，直接备份数据库文件适用于MyISAM和InnoDB存储引擎，由于备份时数据库表正在读写，备份出的文件可能损坏无法使用，不推荐直接使用此方法。

导出数据库sbtest

mysqldump -u root -p --set-gtid-purged=OFF sbtest > /home/sbtest.sql

导出一个表mysql.user

mysqldump -u root -p --set-gtid-purged=OFF mysql user > /home/mysql.user.sql

只导出数据库表结构 -d没有数据 --add-drop-table 在每个create语句前增加一个drop table

mysqldump -u root -p --set-gtid-purged=OFF -d --add-drop-table sbtest > /home/sbtest.sql

恢复数据方法1

mysql -u root -p sbtest < /home/sbtest.sql

恢复数据方法2

mysql -u root -p

use sbtest;

source /home/sbtest.sql

3.2 另外一种可以实时备份的开源工具为xtrabackup

http://www.percona.com/downloads/提供下载

Xtrabackup是由 Percona 开发的一个开源软件，可实现对 InnoDB 的数据备份，支持在线热备份（备份时不影响数据读写）。备份时，Xtrabackup 会将 Master 的 binlog 信息记录在 xtrabackup_slave_info 文件中，通过此信息可以方便的搭建主从复制。

XtraBackup 有两个工具：xtrabackup 和 innobackupex：
xtrabackup 本身只能备份 InnoDB 和 XtraDB ，不能备份 MyISAM；
innobackupex 本身是 Hot Backup 脚本修改而来，同时可以备份 MyISAM 和 InnoDB，但是备份 MyISAM 需要加读锁。

官网：http://www.percona.com/software/percona-xtrabackup
文档：http://www.percona.com/doc/percona-xtrabackup/2.2/index.html

安装 XtraBackup（主从库都要操作）

vi /etc/resolv.conf

nameserver 192.168.1.1
nameserver 8.8.8.8

1、添加源

yum install https://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

2、安装 xtrabackup

从这个位置E:\zachary\mysql\上传以下两个软件到服务器的/software目录

libev-4.15-3.el7.x86_64.rpm
Percona-XtraBackup-2.4.11-rb4e0db5-el7-x86_64-bundle.tar

cd /software

rpm -ivh libev-4.15-3.el7.x86_64.rpm

tar -xvf Percona-XtraBackup-2.4.11-rb4e0db5-el7-x86_64-bundle.tar

yum install rsync perl-DBD-MySQL perl-Digest-MD5

rpm -ivh percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

创建备份

innobackupex --user=root --password=jge64NSU /home

如果执行正确，其输出信息通常类似：
180602 07:05:25 Executing UNLOCK TABLES
180602 07:05:25 All tables unlocked
180602 07:05:25 Backup created in directory '/home/2018-06-02_07-05-20/'
MySQL binlog position: filename 'master-bin.000009', position '120'
180602 07:05:25 [00] Writing /home/2018-06-02_07-05-20/backup-my.cnf
180602 07:05:25 [00] ...done
180602 07:05:25 [00] Writing /home/2018-06-02_07-05-20/xtrabackup_info
180602 07:05:25 [00] ...done
xtrabackup: Transaction log of lsn (1684441) to (1684441) was copied.
180602 07:05:25 completed OK!

备份时，innobackupex 会调用 xtrabackup 备份 InnoDB 表的数据，并且会复制 MyISAM, MERGE，CSV 和 ARCHIVE 表的表定义文件（.frm 文件）、数据文件。同时还会备份触发器和数据库配置信息相关的文件。这些文件将会保存在指定备份目录中一个以时间戳命名的目录下。

准备备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobackupex --apply-log /home/2018-06-02_07-05-20

如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: page_cleaner: 1000ms intended loop took 8226ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1685544
180602 07:15:20 completed OK!

在实现“准备”的过程中，innobackupex 通常还可以使用 --use-memory 选项来指定其可以使用的内存的大小，默认通常为 100M。如果有足够的内存可用，可以多划分一些内存给 prepare 的过程，以提高其完成速度。

恢复备份

将数据复制到从服务器上：
scp -r /home/2018-06-02_07-05-20 root@192.168.1.12:/home

在从服务器中恢复备份数据：（从库上操作）

service mysql stop

mv -f /var/lib/mysql /var/lib/`date +%Y_%m%d`_mysql_BACKUP

mkdir /var/lib/mysql

chown mysql:mysql /var/lib/mysql

innobackupex --datadir=/var/lib/mysql --copy-back /home/2018-06-02_07-05-20

如果服务器剩余空间不足，你可以使用 --move-back 替换掉 --copy-back。

如果执行正确，其输出信息的最后几行通常如下：
180602 08:22:12 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /var/lib/mysql/performance_schema/session_account_connect_attrs.frm
180602 08:22:12 [01]        ...done
180602 08:22:12 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
180602 08:22:12 [01]        ...done
180602 08:22:12 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
180602 08:22:12 [01]        ...done
180602 08:22:12 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id
180602 08:22:12 [01]        ...done
180602 08:22:12 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
180602 08:22:12 [01]        ...done
180602 08:22:12 completed OK!

chown -R mysql:mysql /var/lib/mysql

chmod -R g+w /var/lib/mysql

chmod -R o+x /var/lib/mysql/mysql

chcon -R -t mysqld_db_t /var/lib/mysql

service mysql start

4.mysql复制

复制功能可以经济高效地提高应用程序的性能、扩展力和高可用性。既支持简单的主从拓扑，也可以实现复杂、极具可伸缩性的链式集群。

使用mysql复制时，所有对复制表的更新必须在主服务器上进行，否则可能引起主服务器上的表进行的更新与对从服务器上的表所进行的更新产生冲突。

好处：（1）主从数据同步，主服务器故障时，从服务器可作为主服务器接管服务。

（2）负载均衡。实现读写分离，主服务器记录更新、删除、插入等操作；从服务器只查询请求；

（3）数据备份。从服务器可设置在异地，增加容灾的健壮性，为避免传输过慢，可设置参数slave_compressed_protocol 启用binlog压缩传输

show global variables like '%slave_compressed_protocol%';

+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| slave_compressed_protocol | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)

set global slave_compressed_protocol=on;

show global variables like '%slave_compressed_protocol%';

+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| slave_compressed_protocol | ON |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql使用3个线程来执行复制功能，1个在主服务器上，2个在从服务器上。当执行START SLAVE时，主服务器创建一线程负责发送二进制日志。从服务器创建一个I/O线程，负责读取主服务器上的二进制日志，然后将该数据保存到从服务器数据目录中的中继日志文件中。从服务器的SQL线程负责读取中继日志并重做日志中包含的更新，从而达到主从数据库数据的一致性。#在主从服务器上输入命令查询状态：mysql>show processlist \G 其中time列的值可以显示从服务器比主服务器滞后多长时间。

master操作：

slave操作：

启动：

nohup mysqld_safe --defaults-file=/usr/my.cnf 2>&1 > /dev/null &

关闭：

mysqladmin -u root -p -S /var/lib/mysql/mysql.sock shutdown

查看mysql端口：

netstat -tulpn |grep -i mysql

tcp6 0 0 :::3306 :::* LISTEN 8681/mysqld

ss -tulpn |grep -i mysql

tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=8681,fd=15))

登录mysql：

mysql -u root -p -S /var/lib/mysql/mysql.sock

远程登录，指定IP、端口

mysql -u root -p -h 192.168.1.14 -P 3306

清屏：

mysql> system clear;

临时修改mysql提示符：

prompt \u@\h \d \r:\m:\s>

PROMPT set to '\u@\h \d \r:\m:\s>'
root@localhost (none) 04:39:01>

永久修改mysql提示符：（无需重启）

参考：http://www.2cto.com/database/201304/201745.html

或在shell中添加环境变量

vi /etc/profile

MYSQL_PS1="\u@\h \d \r:\m:\s>";export MYSQL_PS1

su -

刷新权限

flush privileges;

删除用户

drop user 'test2'@'%';

创建用户并授权

grant all privileges on sbtest.* to 'test2'@'localhost' identified by 'test2';

create user 'test3'@'%' identified by 'test3';

grant all privileges on sbtest.* to 'test3'@'%';

查看权限

show grants for 'test2'@'localhost';

+--------------------------------------------------------------------------------------------------------------+
| Grants for test2@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test2'@'localhost' IDENTIFIED BY PASSWORD '*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E' |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO 'test2'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

show grants for 'test3'@'%';

+------------------------------------------------------------------------------------------------------+
| Grants for test3@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test3'@'%' IDENTIFIED BY PASSWORD '*F357E78CABAD76FD3F1018EF85D78499B6ACC431' |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO 'test3'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql日常维护

MySQL重要目录

数据库目录　/var/lib/mysql
配置文件   　 /usr/share/mysql
命令目录    　/usr/bin(mysqladmin mysqldump等命令)
启动脚本   　 /etc/rc.d/init.d/mysql

重新启动mysql服务

service mysql restart

Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!

检查mysql3306状态

netstat -nat

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN
tcp        0     52 192.168.1.11:22         192.168.1.10:49576      ESTABLISHED
tcp6       0      0 :::3306                 :::*                    LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN
tcp6       0      0 ::1:25                  :::*                    LISTEN
tcp6       0      0 192.168.1.11:3306       192.168.1.14:5464       ESTABLISHED
tcp6       0      0 192.168.1.11:3306       192.168.1.12:21576      ESTABLISHED

检查mysql活动状态

mysqladmin -u root -p ping

Enter password:
mysqld is alive

返回服务器状态

mysqladmin -u root -p status

Enter password:
Uptime: 632 Threads: 3 Questions: 30 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.047

关闭MySQL服务

mysqladmin -u root -p shutdown

service mysql start

显示服务器版本

mysqladmin -u root -p version

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.6.40-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 4 min 53 sec

Threads: 3 Questions: 30 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.102

修改mysql管理员密码

mysqladmin -u root -p password 'jge64NSU' ###单引号里面的为新密码

Enter password: 输入旧密码
Warning: Using a password on the command line interface can be insecure.

mysql添加新用户

格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"

grant select,insert,update,delete on *.* to 'test2'@'%' identified by 'test2';

grant all on sbtest.* to 'test3'@'localhost' identified by 'test3';

'test2'@'%'=所有网络
'test3'@'localhost'=只能本地服务器登录
'test3'@'192.168.0.10'=指定IP登录
'test3'@'192.168.0.%'=192.168.0/254网络登录

mysql撤销用户权限

格式： revoke 权限 on 数据库.表 from 用户名@登录客户端;

revoke insert,delete on *.* from 'test2'@'localhost';

revoke all on sbtest.* from 'test3'@'localhost';

mysql删除用户

drop user 'test2'@'localhost';

drop user 'test3'@'localhost';

查看mysql用户

select host,user,password from mysql.user;

查看连接服务器用户线程

show processlist;

杀掉用户线程

mysql> kill 11;

ERROR 1317 (70100): Query execution was interrupted

mysql数据库备份恢复

显示数据库sbtest内容

mysqldump -u root -p --set-gtid-purged=OFF sbtest |more

备份多个表，备份数据库sbtest的表test和表test_log

mysqldump -u root -p --set-gtid-purged=OFF sbtest test test_log > /home/sbtest.test_test_log.sql

备份单一数据库sbtest

mysqldump -u root -p --set-gtid-purged=OFF sbtest > /home/sbtest.sql

备份多个数据库，备份数据库sbtest和数据库mysql

mysqldump -u root -p --set-gtid-purged=OFF -B sbtest mysql > /home/sbtest_mysql.sql

备份所有数据库

mysqldump -u root -p --set-gtid-purged=OFF --all-databases > /home/all-databases.sql

恢复数据库

mysql -u root -p

use sbtest;

source /home/sbtest.sql;

原文地址https://blog.csdn.net/zacharyzhong/article/details/81132110

MySQL日常维护（超级大杂烩）

MySQL日常维护（超级大杂烩）

热门文章

最新文章

相关课程

相关电子书

相关实验场景