MySQL 命令行辅助 mycli

Install

Mycli is tested on macOS and Linux. It runs on Python 2.7 and 3.4+.

NOTE: Python 2.6 support was dropped in mycli 1.9.0. If you’re running Python 2.6, you’ll want to install mycli 1.8.1.

######Python Package:

If you already know how to install python packages, then you can do:

  • You might need sudo.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$ pip install mycli
or
$ easy_install mycli

# Windows:
# Follow the instructions on this blogpost to install mycli on Windows: https://www.codewall.co.uk/installing-using-mycli-on-windows/

# macOS:
#The easiest way install mycli on a Mac is to use Homebrew.
$ brew install mycli


# Linux:
# Debian/Ubuntu Package:
# https://packages.debian.org/search?keywords=mycli
$ sudo apt-get update
$ sudo apt-get install mycli

# Fedora
$ sudo dnf install mycli


# RHEL, Centos:
# We don't have packages for RHEL or Centos, yet. Instead, use pip to install mycli. You can install pip on your system using:
$ sudo yum install python-pip python-devel
# Once that is installed, you can install mycli:
$ sudo pip install mycli
Install Error QA.
1
Cannot uninstall 'configobj'. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.
Fix code
1
pip install --ignore-installed mycli

How to install MariaDB 10.1 on CentOS 7.x

CentOS 7.x 默认的MariaDB版本是5.5,但是在有些情况我们需要用到10版本,注意MariaDB与MySQL版本是不一样的,下面简单降下如何快速安装MariaDB10.1 到CentOS 7.x.

创建MariaDB 10.1的Yum源

1
2
3
4
5
6
7
8
9
cat <<EOF | sudo tee -a /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.1 CentOS repository list
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

注意MariaDB会随着时间的推移版本不断更新,你可以到repo仓库获取对应版本链接

使用Yum安装MariaDB 10.1 即可

1
sudo yum install MariaDB-server MariaDB-client -y

启动MariaDB并设置为开机自启动

1
2
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service

初始化MariaDB设置

1
2
3
4
5
6
7
8
9
10
11
12
sudo /usr/bin/mysql_secure_installation

# 回答以下问题,请记住你的数据库root用户密码:

Enter current password for root (enter for none): 回车
Set root password? [Y/n]: Y
New password: 你要设置的数据库root密码
Re-enter new password: 你要设置的数据库root密码
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

HowTo-Automatic-EasyBackup-MysqlDB

mysql是一个免费、开源中一款非常优秀关系型数据库,在现在的互联网中使用的非常广泛,无论是大型IT项目还是个人开发者的小项目,mysql都能很好的协助人们处理数据库相关的工作,同时数据库对于我们来说是非常重要,所以经常备份数据库是一个基本的操作,这会为你或者你的团队,减少非常多不必要的麻烦。

mysqldump是一个简单而且非常流行的mysql全量备份方式,配合crontab添加自动备份任务,很好的完成了我们针对数据库备份的需求,下面我会通过一个例子来说明如何完成这项操作。

mysqldump是mysql自带的备份工具,所以只要你安装mysql应用包,就无需单独安装mysqldump

测试环境

我搭建了一个Ghost博客环境,数据库采用是的Mysql,接下来我想在每天00:00执行数据库备份操作,并在备份完成之后,告诉我是否备份成功。

所以我们的步骤应该:

  1. 测试备份命令是否可以正常执行
  2. 安装测试命令行邮件工具mailx
  3. 安装计划任务工具Crontab
  4. 编写备份脚本
  5. 添加计划任务

测试备份命令

首先你要获得你要备份的数据库对应的select权限,仅需要select权限即可,mysql在管理方面,应该坚持只赋予必须权限的原则。

1
2
mysql> grant select on ghost.* to 'ghost_backuser'@'localhost' identified by 'backupPass';
Query OK, 0 rows affected (0.00 sec)

因为我只在本地执行备份操作,所以我只赋予了localhost的权限,你的权限应该要是执行备份工作的服务器主机信息。

1
2
3
4
5
6
7
8
9
10
# 创建仅授权本地访问的用户
mysql> create user dbackuser@'localhost';
# 创建授权所有来源地址的用户
mysql> create user dbackuser@'%';
# 创建仅授权从特定IP的用户
mysql> create user dbackuser@'192.168.0.230';
# 创建仅授权从特定IP段访问的用户
mysql> create user dbackuser@'192.168.0.0/23';
# 创建仅授权从特定域名来访问的用户
mysql> create user dbackuser@'samzong.me';

好了,接下来我们测试对应用户是否有权限。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
➜  ~ mysql -u ghost_backuser -pbackupPass
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ghost |
+--------------------+
2 rows in set (0.00 sec)

mysql> use ghost;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select name from users;
+------+
| name |
+------+
| ALEX |
+------+
1 row in set (0.00 sec)

测试mysqldump备份命令,注意mysqldump备份会锁表,但对于正在工作的数据库,锁表会影响到正常业务,所以我们可以使用–single-transaction参数,不锁表备份。

1
2
3
4
5
6
7
8
9
➜  ~ mysqldump -u ghost_backuser -pbackupPass ghost > ghost.bak.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1044: Access denied for user 'ghost_backuser'@'localhost' to database 'ghost' when using LOCK TABLES
➜ ~ mysqldump -u ghost_backuser -pbackupPass --single-transaction ghost > ghost.bak.sql
Warning: Using a password on the command line interface can be insecure.
➜ ~ ls -lh
total 780K
-rw-r--r-- 1 root root 780K May 17 16:24 ghost.bak.sql
➜ ~

安装命令行邮件工具mailx

安装mailx 在CentOS/RehHat:

1
➜  ~ yum install -y mailx

测试发送邮件:

1
➜  ~ echo "test" | mail -s "this a test email" samzong.lu@gmail.com

安装计划任务工具Crontab

crontab命令常见于Unix和类Unix的操作系统之中,用于设置周期性被执行的指令。该命令从标准输入设备读取指令,并将其存放于“crontab”文件中。通常,crontab储存的指令被守护进程激活, crond常常在后台运行,每一分钟检查是否有预定的作业需要执行。这类作业一般称为cron jobs。

1
2
➜  ~ yum install vixie-cron
➜ ~ yum install crontabs

vixie-cron软件包是cron的主程序;

crontabs软件包是用来安装、卸装、或列举用来驱动 cron 守护进程的表格的程序。

启动crond并设置为开机自启动:

1
2
3
➜  ~ service crond start
Starting crond: [ OK ]
➜ ~ chkconfig crond on

crontab 基础命令:

1
2
3
4
5
6
7
8
9
10
11
语  法:crontab [-u <用户名称>][配置文件] 或 crontab [-u <用户名称>][-elr]

补充说明:cron是一个常驻服务,它提供计时器的功能,让用户在特定的时间得以执行预设的指令或程序。只要用户会编辑计时器的配置文件,就可以使用计时器的功能。

配置文件格式:Minute Hour Day Month DayOFWeek Command

参  数:
-e  编辑该用户的计时器设置。
-l  列出该用户的计时器设置。
-r  删除该用户的计时器设置。
-u<用户名称>  指定要设定计时器的用户名称。

crontab 配置的基本格式:

1
2
3
4
5
6
7
8
9
*     *   *   *   *  command
分  时  日  月  周  命令

第1列表示分钟1~59 每分钟用*或者 */1表示
第2列表示小时1~23(0表示0点)
第3列表示日期1~31
第4列表示月份1~12
第5列标识号星期0~6(0表示星期天)
第6列要运行的命令

crontab 的一些例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#每晚的21:30 重启apache
30 21 * * * /usr/local/etc/rc.d/lighttpd restart

#每月1、10、22日的4 : 45重启apache
45 4 1,10,22 * * /usr/local/etc/rc.d/lighttpd restart

#每周六、周日的1 : 10重启apache
10 1 * * 6,0 /usr/local/etc/rc.d/lighttpd restart

#每天18 : 00至23 : 00之间每隔30分钟重启apache
0,30 18-23 * * * /usr/local/etc/rc.d/lighttpd restart

#每星期六的11 : 00 pm重启apache
0 23 * * 6 /usr/local/etc/rc.d/lighttpd restart

#晚上11点到早上7点之间,每隔一小时重启apache
* 23-7/1 * * * /usr/local/etc/rc.d/lighttpd restart

#每一小时重启apache
* */1 * * * /usr/local/etc/rc.d/lighttpd restart

#每月的4号与每周一到周三的11点重启apache
0 11 4 * mon-wed /usr/local/etc/rc.d/lighttpd restart

#一月一号的4点重启apache
0 4 1 jan * /usr/local/etc/rc.d/lighttpd restart

#每半小时同步一下时间
*/30 * * * * /usr/sbin/ntpdate 210.72.145.44

编写备份脚本

好了,以上我们测试需要用到的各个模块,下面我们要编写备份脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/bin/bash
# mysqldump scripts.
# filepath: /usr/local/bin/ghost_sqldump.sh
# Author: samzong

# set TIME variable
TIME=`date "+%Y%m%d%H%M%S"`

# backup db ghost to /mysqlbak/
mysqldump --single-transaction -h localhost -u ghost_backuser -pbackupPass ghost > /mysqlbak/ghost_$TIME.sql

# tar sql files.
tar czvf /mysqlbak/ghost_$TIME.sql.tgz /mysqlbak/ghost_$TIME.sql --remove-files

# remove over 7 days sql files.
find /mysqlbak/ -mtime +7 -exec rm -f {} \;

# send mial to admin'mial
if [ $? -eq 0 ]
then
echo "ghost SQL dump is successfully. At time: `date` " | mail -s ghost-dump-successfully samzong.lu@gmail.com
else
echo " Error Error ghost SQL dump is Error. At time: `date` " | mail -s ghost-dump-error samzong.lu@gmail.com
fi

注意脚本中的以下内容要根据你的实际情况修改:

  • -h “ “ 这是数据库所在的主机
  • -u “ “ 这是数据库可备份的用户名
  • -p” “ 备份用户的密码

添加计划任务

经过以上测试,已经很好的完成备份脚本,接下来将脚本添加到crontab内,并设置自动执行的时间:

1
2
➜  ~ crontab -e
00 00 * * * sh /usr/local/bin/ghost_sqldump.sh

重启crontab服务,并确认crontab已经设置为开机自启动

1
2
3
4
5
➜  ~ service crond restart
Stopping crond: [ OK ]
Starting crond: [ OK ]
➜ ~ chkconfig --list | grep crond
crond 0:off 1:off 2:on 3:on 4:on 5:on 6:off

MySQL(7) 索引创建、删除和查看

1. 索引的作用

在索引列上,数据库利用各种各样的快速定位技术,能够大大的提高查询效率,特别是当数据量非常大和查询设计到多个表时,索引的利用能够将效率提高成千上万倍,当然要合理的利用索引。

2. 创建索引

​ 在执行CREATE TABLE时可以创建索引,也可以在之后单独使用CREATE INDEX或ALTER TABLE来为表增加索引。

2.1 ALTER TABLE

​ ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

1
2
3
ALTER TABLE tb_name ADD INDEX index_Name (column_list);
ALTER TABLE tb_name ADD UNIQUE (column_list);
ALTER TABLE tb_name ADD PRIMARY KEY (column_list);

​ tb_name 是要增加索引的表名,column_list 是要增加索引的列,多列用逗号隔开,index_Name 是可选,缺省为第一个索引列赋名称,ALTER TABLE允许在单个语句中更改多个表,因此可以也可以同时创建多个索引。

2.2 CREATE INDEX

​ CREATE INDEX 可对表增加普通索引或者UNIQUE索引。

1
2
CREATE INDEX index_Name ON tb_name(column_list);
CREATE UNIQUE INDEX index_Name ON tb_name(column_list);

​ tb_name、index_Name和column_list具有ALTER TABLE语句中相同的含义,索引名称不可自定义,也不能创建PRIMARY KEY。

3. 索引类型

​ 在创建索引时,可以规定索引能否包含重复的值,如果不包含,则索引应该为PRIMARY KEY或UNIQUE索引,对于单列唯一索引,这能保证单列不包含重复的值,对于多列唯一索引,保证多值的组合不重复。

​ PRIMAY KEY索引与UNIQUE索引非常类似,实际上PRIMAY KEY索引仅是一个具有名称PRIMAY的UNIQUE索引,这表示一个表只能包含一个PRIMAY KEY索引,因为一个表中不可能具有两个同名的索引。

4. 删除索引

​ 可以使用ALTER TABLE或DROP INDEX语句来删除索引:

1
2
3
DROP INDEX index_Name On tb_name;
ALTER TABLE tb_Name DROP TABLE index_Name;
ALTER TABLE tb_Name DROP PRIMAY KEY;

​ 第一条和第二条作用是一样的,删除tb_Name中index_Name索引;第三条仅适用于删除PRIMAY KEY时,因为一个只可能有一个PRIMAY KEY索引。

​ 另外,如果在表中删除了某列,则索引会受到影响,对于多列组合的索引,如果删除其中某列,则该列也会在索引中删除,如果删除所有列,则该索引也会被删除。

5. 查看索引

1
2
mysql> show index from tb_name;
mysql> show keys from tb_name;
  • Table : 表名
  • Non_unique: 如果索引不能包含重复值为0,如果可以为1
  • Key_name: 索引的名称
  • Seq_in_index:索引中列的序号,从1开始
  • Column_name: 列的名称
  • Collation:列以什么方式存储在索引中,在MySQL中有值‘A’(升序)和NULL(无分类)
  • Cardinality: 索引中唯一值的数量估计值,通过运行ANALYZE TABLE或者 myisamchk -a 可以更新,基数根据被存储的整数的统计数据来计数,所以,即使对于小型表,该值也没有必要是精确的,基数越大,当进行联合时,MySQL使用该索引的机会就越大。
  • Sub_part: 如果列只是被部分编入,则为编入索引的字符数目,如果整列被编入索引,则为NULL
  • Packed:关键字如何被压缩,没有压缩为NULL
  • Null: 如果列含有NULL为YES,如果没有则为NO
  • Index_type: 用过的索引方法,BTREE,FULLTEXT,HASH,RTREE
  • Comment: 备注

每日备份MySQL单表数据

最近愈近年底,客户业务涉及对账操作,所以同事提出需求需要单独备份某张表数据,并且每日备份;思考之后解决办法如下:

  1. mysqldump导出单张表的表结构及数据
  2. 新增数据库,专做临时每日数据备份库
  3. 导入该表数据到新增数据库内
  4. 重命名导入的表名(预防计划任务下次导入时数据覆盖)
  5. 编写脚本,添加Linux计划任务

1. mysqldump导出但张表的表结构及数据

1
mysqldump -h dbServer -PdbServerPort -u db_user -p -d dbName tbName > tbName.sql

2. 新增数据库,专做临时每日数据备份库

1
2
# loginTo your DBserver
mysql> create database bakdbName character set utf8;

3. 导入该表数据到新增数据库内

1
mysql -h dbServer -P dbServerPort -u db_user -p bakdbName < tbName.sql

4 . 重命名导入的表名

1
mysql -u db_user -p -e "rename table bakdbName.tbName to bakdbName.tbName_$DATE"

5. 编写脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@testServer01 ~]# cat /usr/local/bin/bakTable.sh
#!/bin/bash

#Create by SAMZONG

DATE=`date +%Y%m%d`
TMPDIR=/tmp/baksql

# modify your DB configure
DBSERVER1=localhost
DBSERVER1_PORT=3306
DBSERVER1_USER=root
DBSERVER1_PASSWORD=password
MASTER_DBNAME=zabbix
MASTER_TBNAME=users

DBSERVER2=localhost
DBSERVER2_PORT=3306
DBSERVER2_USER=root
DBSERVER2_PASSWORD=password
BAKDBNAME=z3

# creat tmp folder
if [ ! -d $TMPDIR ]; then
mkdir $TMPDIR
fi

# dump tbName
mysqldump -h $DBSERVER1 -P $DBSERVER1_PORT -u $DBSERVER1_USER -p"$DBSERVER1_PASSWORD" -d $MASTER_DBNAME $MASTER_TBNAME > $TMPDIR/$MASTER_TBNAME.sql


# insert tbNAME to bakdbName
if [ $? -eq 0 ]; then
mysql -h $DBSERVER2 -P $DBSERVER2_PORT -u $DBSERVER2_USER -p"$DBSERVER2_PASSWORD" $BAKDBNAME < $TMPDIR/$MASTER_TBNAME.sql

if [ $? -eq 0 ]; then
# rename tbName
mysql -h $DBSERVER2 -P $DBSERVER2_PORT -u $DBSERVER2_USER -p"$DBSERVER2_PASSWORD" -e "rename table "$BAKDBNAME"."$MASTER_TBNAME" to "$BAKDBNAME"."$MASTER_TBNAME"_"$DATE";"
fi

fi

6. 添加到Linux计划任务

1
00 00 * * * /usr/local/bin/bakTable.sh

HowTo Reset MySQL Root Password

1. 处理的状态

首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。
因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的
状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对
外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全
状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。

2. 重置密码

  1. 修改mysql配置文件,增加skip-grant-tables

    1
    2
    3
    [root@demo ~]# vim /etc/my.cnf
    [mysqld]
    skip-grant-tables
  2. 重启mysqld

    1
    2
    3
    [root@demo ~]# service mysqld restart
    Stopping mysqld: [ OK ]
    Starting mysqld: [ OK ]
  3. 登录mysql,并修改root密码.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    [root@demo ~]# mysql -u root
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.5.53 MySQL Community Server (GPL)

    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> use mysql
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> update user set password=password('new-pass') where user='root';
    Query OK, 5 rows affected (0.00 sec)
    Rows matched: 5 Changed: 5 Warnings: 0
  4. 配置文件去除skip-grant-tables,并重启服务器.

    1
    2
    [root@demo ~]# vim /etc/my.cnf
    [root@demo ~]# service mysqld restart
  5. 使用新密码登录到mysql

    1
    [root@demo ~]# mysql -h localhost -u root -p

MySQL(5) 清空表命令之:truncate与delete区别

       在实际工作中,有时我们会想要去清空一张表的所有数据,常用的命令有delete和truncate这两个命令。

1. delete

       在很多情况,我们对于delete的应用更多,因为它支持更多的匹配模式,我们可以使用where条件语句,清理一些特定的数据,关于delete的操作,这里不做过多解释,有空就专门写写delete的使用和原理。delete 在清空表时,如果表内有自增ID的设定,那么在delete清空之后,再插入数据时,自增ID不会从1开始,默认会继续增加; 除非我们使用OPTIMIZE TABLE,重置表属性;

1
2
mysql> delete from piwik_tmp;
Query OK, 0 rows affected (0.00 sec)

2. truncate

       truncate 在清空表时,会同时将自增ID重置,再插入新数据时会从1开始,而且truncate在清空表时,不关心表的行数,所以执行效率会高于delete;

1
2
mysql> truncate table piwik_tmp;
Query OK, 0 rows affected (0.01 sec)

3. 释放表空间

       但是在truncate清空表数据时,myisam的表和innodb的表在使用上有一定的区别;myisam表会清空所有数据,并释放表空间,即硬盘空间会得到释放。innodb表也会清空所有数据,但不释放表空间。Innodb数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,这就导致InnoDB数据库文件不断增长。如果想彻底释放这些已经删除的数据,需要把数据库导出,删除InnoDB数据库文件,然后再导入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 备份数据库:
mysqldump -u -p --quick --force --all-databases > mysqldump.sql

# 停止数据库
service mysqld stop

# 删除这些大文件
rm /usr/local/mysql/var/ibdata1
rm /usr/local/mysql/var/ib_logfile*

# 手动删除除Mysql之外所有数据库文件夹,然后启动数据库
service mysqld start

# 还原数据
mysql -uroot -proot < mysqldump.sql

4. innodb_file_per_table

       还有一种方式是在创建数据库的时候设置innodb_file_per_table,这样InnoDB会对每个表创建一个数据文件,然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。

4.1 修改mysql默认配置文件。

       编辑my.ini或my.cnf 在innodb段中加入 innodb_file_per_table=1 # 1为启用,0为禁用

4.2 查看修改结果

       通过mysql语句可以查看该变量的值:

1
mysql> show variables like '%per_table%';

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×