centOs下升级mysql

用法

mysql  -uxxx    –pxxx   -e  “mysql 命令”

当然还可以使用 如下写法

mysql  -e  -uxxx    –pxxx    “mysql 命令”

使用shell命令操作数据库,shell命令数据库

使用mysql的-e参数可以执行各种sql的(创建,删除,增,删,改、查)等各种操作

mysqldump 逻辑备份的正确姿势

  1. 利用mysqldump进行逻辑备份

 

1)全逻辑备份:

 

mysqldump -uxxx -p –flush-logs –delete-master-logs –all-databases
> alldb.sql (每天晚上进行一次全备)

 

2)增量备份:

 

mysqladmin flush-logs (每小时刷一下,保存起来,进行了一次增量备份)

 

3)缺点:

 

1> –all-databases 包含了 mysql
数据库,其中包含了权限的数据,所以我们应该加上
–flush-privileges,在恢复时,权限才能生效;

 

注意 –all-databases 包括了mysql数据库,但是不会包含
information_schema和performance_schema两个数据库。

 

2> 因为 mysqldump 默认启用了
–lock-tables,所以会导致在备份期间对所有表持有读锁: lock table tb read
local,所以所有的update,delete语句

 

会被阻塞。但是select语句和insert语句不会被阻塞。

 

3> –delete-master-logs 备份之后,会执行 purge logs to
语句。删除了备份之后的master上的binary log.
一般而言,我们不建议随便删除binary log.

 

我们应该将它们保存起来,而不是直接删除。以防万一,要留条退路。

 

4> 该备份方式,虽然在整个备份过程中持有了 lock table tb read
local,但是还是可以执行 insert
语句的。所以得到的不是一致性的备份。虽然得到的不是

 

一致性的备份,但是因为flush log之后,所有的操作 也会记入新的binary
log,所以如果使用了所有新的binary log来进行完全恢复的话,最后恢复的数据

 

也是一致性的。当然不一致性的备份无法用于搭建slave。

 

如果要得到一致性的备份的话,需要使用 –lock-all-tables 或者使用
–single-transaction
选项。前者使用了全局读锁,不允许任何修改操作。后者使用

 

了事务的特性来得到一致性备份。

 

所以我们应该对上面的备份方式进行改良。

 

  1. 使用mysqldump备份的最佳姿势

 

1)优化锁 和 得到一致性备份:

 

我们可以使用结合使用 –single-transaction 、–master-data=2
、–flush-logs
来达到将锁定时间大大减少的目的。同时有得到了一致性的备份,而且该一致性备份和
flush 的日志也是一致的;

 

2)去掉 –delete-master-logs 选项,改为在备份之后,将所有被刷新的 binary
log 移到一个地方保存起来;

 

3)因为使用了 –single-transaction 选项,针对的只能是 innodb
数据,但是mysql数据是Myisam引擎的,所以我们最好将mysql数据库的备份分开来,

 

另外专门针对 mysql
数据库进行一次操作。当然不分开来备份,可能也没有问题。

 

4)还要加上 –routines 来备份存储过程和函数,触发器默认会备份。

 

优化之后,我们得到:

 

mysqldump -uxxx -p –single-transaction –master-data=2 –routines
–flush-logs –databases db1 db2 db3 > alldb.sql;

 

mysqldump -uxxx -p –flush-privileges –databases mysql > mysql.sql;

 

如果将mysql也一起备份的话:

 

mysqldump -uxxx -p –single-transaction –master-data=2 –routines
–flush-logs –flush-privileges –all-databases > alldb.sql;

 

  1. 使用mysqldump来搭建slave环境

 

搭建slave环境,一般有两种方法,对于规模不大的库,可以采用mysqldump来搭建;对于规模很大的库,最好采用xtrabackup来搭建,速度要快很多。

 

1)首先
分别在master和slave上设置不同的server_id=1/101,启用master上的log-bin=1,启用slave上的relog-log=relay-bin;
在master上设置:

 

binlog_format=row;二进制日志的格式。maser上最好还设置sync_binlog=1 和
innodb_flush_log_at_trx_commit=1防止发生服务器崩溃时

 

导致复制破坏。在slave上最好还配置:read-only=1 和 skip-slave-start=1
前者可以防止没有super权限的用户在slave上进行写,后者防止在启动

 

slave数据库时,自动启动复制线程。以后需要手动start
slave来启动复制线程。注意slave没有必要启用
log-bin=1,除非需要搭建二级slave。

 

2)在master上建立一个具有复制权限的用户:

 

grant replication slave, replication client on *.* to
[email protected]’192.168.%.%’
identified by ‘123456’;

3)备份master上的数据库,迁移到slave上:

[[email protected] ~]# mysqldump -uroot -p --routines --flush-logs --master-data=2 --databases db2 db1>/root/backup.sql
Enter password:
[[email protected] ~]# scp /root/backup.sql 192.168.137.9:/tmp/backup.sql
The authenticity of host '192.168.137.9 (192.168.137.9)' can't be established.
RSA key fingerprint is a4:cd:c0:13:d1:8c:c0:a5:e7:c4:43:b5:95:17:af:d3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.137.9' (RSA) to the list of known hosts.
[email protected]'s password:
backup.sql  

因为slave的搭建需要一致性的备份,所以需要启用
–lock-all-tables(master-data=1/2会自动启用–lock-all-tables)或者–single-transaction;

 

另外还需要知道该一致性备份的数据,对应的master上的binary
log的文件名,以及在该文件中的position,所以必须启用 master-data选项。

 

因为–master-data会启用–lock-all-tables
所以数据才是一致性的;但是导致了全局锁,不能进行任何修改操作;下面我们使用–single-transaction进行优化:

 

mysqldump -uroot -p –routines –flush-logs –single-transaction
–master-data=2 –databases db1 db2 > /root/backup.sql;
(–flush-logs非必须)

 

这样全局锁仅仅在备份的开始短暂的持有。不会再备份的整个过程中持有全局锁。

 

4)在slave上执行备份的脚本,然后连上master,开启复制线程:

 

执行sql脚本:

 

mysql> source /tmp/backup.sql

找到 –master-data 输出的 binary log 的文件名和postion:

 

[[email protected] ~]# head -50 /tmp/backup.sql

......
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;

执行 change master to, start slave:

 

在salve上执行命令开始复制:

mysql> change master to master_host=’192.168.137.8′,
master_user=’repl’, master_password=’123456′,

    -> master_log_file=’mysql-bin.000010′, master_log_pos=809;

Query OK, 0 rows affected, 2 warnings (0.09 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

 

最后在slave上查看复制线程的状态:

 

mysql> show slave statusG

… …

   Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

……

slave_IO_Runing 和 slave_sql_runing 状态都是yes表示搭建成功。

 

5)replication涉及到的三个线程:

1> master上的 binlog
dump(dump线程),即读取master上的binlog,发送到slave上的线程。

2> slave上的IO线程:读取slave上的relay log。

3> slave上的sql线程:执行IO线程读取的relay log的线程。 

 

  1. 使用mysqldump的备份进行 还原

 

下面使用 mysqldump 进行一个备份,然后删除 datadir,
然后使用备份sql脚本和binary log进行还原的过程。

 

1)首先进行一个全备:

 

mysqldump -uroot -p –single-transaction –master-data=2 –routines
–flush-logs –databases gs ngx_lua > gs_ngx_lua_backup.sql;

数据库有两个库: gs , ngx_lua.

 

2)将 备份时刷新之后的 binary log 利用 mv
命令移动到安全的位置,也就是–master-data=2输出的日志文件,它之前的日志文件都存储到安全的位置:

 

[[email protected] ~]# head -n 50 gs_ngx_lua_backup.sql
-- MySQL dump 10.13  Distrib 5.6.26, for linux-glibc2.5 (i686)
--
-- Host: localhost    Database: gs
... ...--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120;

 

 

也就是将 MASTER_LOG_FILE=’mysql-bin.000027′
之前的日志都存储到其它位置。

 

然后执行:purge binary logs to ‘mysql-bin.000027’ 更新了 mysql-bin.index
中的索引信息,这里并没有删除binary log,因为它们已经被mv走了。

 

3)下面模拟一个 增量备份:

 

 

mysql> delete from user where id=5;
Query OK, 1 row affected (0.02 sec)

mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name| sex  | phone  | addr|
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy|    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
|  4 | lexin    |    1 | 36565634    | shenzhen |
+----+----------+------+-------------+----------+
4 rows in set (0.00 sec)


mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name    | File_size |
+------------------+-----------+
| mysql-bin.000027 |  370 |
| mysql-bin.000028 |  120 |
+------------------+-----------+
2 rows in set (0.00 sec)

 

 

这里 flush logs 进行增量备份,然后将增量备份的 bianry log 文件
mysql-bin.000027 也存储起来。

 

然后在进行一条 delete 语句:

 

 

mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name| sex  | phone  | addr|
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy|    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
+----+----------+------+-------------+----------+
3 rows in set (0.00 sec)

 

到这里数据库的最新状态是:user 表只有3条记录。

 

然后我们同样将 mysql-bin.000028 也存储起来。

 

4)然后我们删除 data-dir 目录中的所有文件,然后开始还原:

 

[[email protected]
mysql]# pwd

/var/lib/mysql

[[email protected]
mysql]# mv ./* /backup/before_delete/

此时 data-dir 目录是空的。然后我们试图使用 mysqladmin -uroot -p shutdown
来关闭mysqld,发现早不到 mysql.sock 无法关闭,我们只好使用

 

killall mysqld

 

来关闭。

 

5)然后进行数据库的初始化,然后开始还原:

 

进入到 /usr/local/mysql/script 目录,然后执行初始化:

 

./mysql_install_db  –user=mysql –datadir=/var/lib/mysql
–basedir=/usr/local/mysql

 

 ./mysql_install_db

初始化成功之后,执行:

 

/usr/local/mysql/bin/mysql_secure_installation

 

来进行密码设置。这一步可能会报错:找不到 /tmp/mysql.sock
文,解决方法,在/etc/my.cnf 中的[client], [mysql], [mysqld]
都进行下面的设置:

 

socket=/tmp/mysql.sock

 

然后重新执行:/usr/local/mysql/bin/mysql_secure_installation 就行了。

 

初始化完成之后,我们使用备份的sql脚本来进行恢复:

 

[[email protected]
~]# mysql -uroot -p < gs_ngx_lua_backup.sql

执行完成之后,发现 gs 和 ngx_lua
数据库都还原出来了,但是他们的数据不是最新的数据:

 

 

mysql> use gs;
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 * from user;
+----+----------+------+----------------+----------+
| id | name| sex  | phone| addr|
+----+----------+------+----------------+----------+
|  1 | yuanfang |    1 | 14465463786    | hangzhou |
|  2 | Andy|    0 | 14465463786    | beijing  |
|  3 | peter    |    0 | 14465463786    | Hongkong |
|  4 | lexin    |    1 | 36565634  | shenzhen |
|  5 | lexin2   |    1 | 43563436565634 | aomao    |
+----+----------+------+----------------+----------+
5 rows in set (0.01 sec)

 

 

这是因为,我们还没有使用 mysql-bin.000027 和 mysql-bin.000028 两个binary
log。mysql-bin.000027 是我们前面模拟的增量备份,而mysql-bin.000028 是
删除data-dir目录时,最新的binary log。依次应用了这两个binary
log之后,数据库才能恢复到最新的状态。

 

6)应用 binary log:

 

[[email protected]
backup]# pwd

/backup

[[email protected]
backup]# mysqlbinlog mysql-bin.000027 > 27.sql

[[email protected]
backup]# mysqlbinlog mysql-bin.000028 > 29.sql

mysqlbinlog mysql-bin.000027 > 27.sql 得到了 27.sql 和 28.sql
之后,使用 mysql 来执行:

 

mysql -uroot -p < 27.sql; 应用了增量备份的binary log.

 

然后查看数据:

 

mysql> mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name| sex  | phone  | addr|
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy|    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
|  4 | lexin    |    1 | 36565634    | shenzhen |
+----+----------+------+-------------+----------+
4 rows in set (0.00 sec)

 

 

然应用 28.sql :

 

mysql -uroot -p < 28.sql;

 

得到最新的状态:

 

mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name| sex  | phone  | addr|
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy|    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
+----+----------+------+-------------+----------+
3 rows in set (0.00 sec)

 

 

可以看到,成功的还原到了删除 data-dir 目录之前的状态了。

 

  1. mysqldump的 坑

 

如果对 mysqldump 的默认启用的选项不熟悉的话,可能会被默认启用的选项
–add-drop-table 给坑了。因为默认会生成 drop table if exist
语句。可能会导致数据的丢失。  –add-drop-database
默认没有启用。如果不想他生成 drop table 语句,可以加入
–skip-add-drop-table选项,或者 –add-ropt-table=0也可以。

 

  1. 总结:

 

1)逻辑备份的最佳方法:

 

全备:

 

mysqldump -uxxx -p –single-transaction –master-data=2 –routines
–flush-logs –databases db1 db2 db3 > alldb.sql;

 

mysqldump -uxxx -p –flush-privileges –databases mysql > mysql.sql;

 

如果将mysql也一起备份的话:

 

mysqldump -uxxx -p –single-transaction –master-data=2 –routines
–flush-logs –flush-privileges –all-databases > alldb.sql;

 

有时,还需要加入:–default-character-set=utf8/utf8mb4
,该选项一般也可以配置在/etc/my.cnf中。

 

增量备份:flush logs; 然后将binary log存储起来即可。

 

2)搭建slave时的最佳选项:

 

mysqldump -uxxx -p –single-transaction –master-data=2 –routines
–databases db1 db2 db3 > alldb.sql;

 

搭建slave,没有必要 –flush-logs。当然搭建slave的最佳方式是使用
xtrabackup,物理备份。

 

3)使用mysqldump备份的sql脚本还原的方法:

 

先还原数据库,然后应用增量日志和最新日志,binary
log在应用之前需要使用mysqlbinlog命令来处理。

逻辑备份的正确姿势 1.
利用mysqldump进行逻辑备份 1)全逻辑备份: mysqldump -uxxx -p
–flush-logs –delete-master-logs –all-databases alldb.sql
(每天…

centOs下升级mysql

前言:今天在centOS上将mysql版本由5.1升级到5.7的时候出现了各种问题,不过经过一步步的调查解决了问题,下面就记录一下过程,其中经历了很多次的卸载和删除,在这次记录的过程中,我就按照最后成功的一次顺序来记录,对于其中可能出现的一些错误会做特殊的说明。

1.下载Linux的5.7版本,主要需要两个文件

MySQL-server-5.7.4_m14-1.el6.x86_64.rpm
 MySQL-client-5.7.4_m14-1.el6.x86_64.rpm

2.备份数据库文件

mysqldump -uxxx -pxx databasename > databasename.sql

3.停止mysql服务

service mysql stop

4.刚开始使用的yum安装的,使用以下语句进行卸载

yum remove mysql*

5.然后删除mysql旧版本已经存在的文件或者数据,不然mysql在重新安装后会出现各种纠结的问题。

find / -name mysql
ps -ef | grep -i mysql

rm -rf mysql****
kill -9 mysqlid

注意/usr/bin、/usr/share/、/var/lib下有很多mysql开头的文件,都删除掉,还有删除/etc目录下的my.cnf文件
6.如果你是rpm安装的,可以通过以下命令进行删除

rpm -qa | grep -i name
rpm -e mysqlxxxxx

7.卸载删除干净后,再使用以下命令对下载的server和client进行安装

rpm -ivh mysqlxxx

8.我将my.cnf中的一部分进行了优化,按照我们的项目需要做了调整,拿出来做为参照

 [mysqld]
  2 datadir=/var/lib/mysql
  3 socket=/var/lib/mysql/mysql.sock
  4 #user=mysql
  5 
  6 # Disabling symbolic-links is recommended to prevent assorted security risks
  7 symbolic-links=0
  8 #设置2进制日志文件目录
  9 log-bin=mysql-bin

#10行不设置的话,会报一个莫名的错误

 10 server-id=1
 11 #指定索引的缓冲区大小,4G内存下设置256或者384合适
 12 key_buffer_size = 256M
 13 #每打开一个表,都会读入数据到table_open_cache中,
 14 table_open_cache = 256
 15 #用于ORDER BY时的缓冲大小,是针对的每个连接,所以不能太大
 16 sort_buffer_size = 256K
 17 #对表进行顺序扫描的请求
 18 read_buffer_size = 256K
 19 #任意顺序读取行时
 20 read_rnd_buffer_size = 512K
 21 #查询到内容向网络传输时
 22 net_buffer_length = 8K
 23 #保存在缓存中的线程数量,
 24 thread_cache_size = 20
 25 ##由于项目中一些表的增删改相对比较频繁,但是查询也占据了很大的一部分,还需要项目运行后观察一段时间
 26 query_cache_size= 8M
 27 # 和cpu的核心数有2倍的关系,我使用的是云服务器,设置这个参数后就启动不起来了
 28 #thread_concurrency = 12
 29 
 30 #提交事务后数据写入磁盘的时间点,设置为2可以对没有强烈的安全要求机制下,对速度提高很大
 31 innodb_flush_log_at_trx_commit = 2
 32 
 33 #如果使用mysql命令登录客户端的时候报权限错误或者密码的错误,可以通过开启标记跳过验证,直接通过mysql进行连接,然后再修改用户名权限
 34 #skip-grant-tables
 35 
 36 [mysqld_safe]
 37 log-error=/var/log/mysqld.log
 38 pid-file=/var/run/mysqld/mysqld.pid

9.在安装的过程中出现了mysql error
1524错误,没有找出来是什么原因,可以通过设置skip-grant-tables跳过错误,然后又出现了error
1045(28000)错误,我把8步骤中的user=mysql注释掉了,这个过程中还出现了其他一些错误,暂时记不起来了,然后通过以上的设置后,再重启mysql服务后,就可以通过mysql
-uxxx -pxx进行登录了,然后又出现了一个问题error
1820,然后查资料说通过以下操作进行实现

mysql> create database ttt;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password = password("root");
Query OK, 0 rows affected (0.00 sec)

10.ok,终于正常了,然后通过source命令把sql文件导入。

前言
:今天在centOS上将mysql版本由5.1升级到5.7的时候出现了各种问题,不过经过一步步的调查解决了问题,下面就记录一下…

使用mysql的-e参数可以执行各种sql的(创建,删除,增,删,改、查)等各种操作

用法

mysql  -uxxx    –pxxx   -e  “mysql 命令”

当然还可以使用 如下写法

mysql  -e  -uxxx    –pxxx    “mysql 命令”

实践出真知

[root@iZwz99qnmldt4n744noo9gZ ~]# mysql -e "show databases;" -uroot -ppenghui110 >test.txt
[root@iZwz99qnmldt4n744noo9gZ ~]# ls
databases_backup_20170411172702  lnmp1.3-full  lnmp-install.log  test.txt
[root@iZwz99qnmldt4n744noo9gZ ~]# cat test.txt 
Database
information_schema
blog
mysql
performance_schema
question
wordpress
yii2
[root@iZwz99qnmldt4n744noo9gZ ~]# mysql -uroot -ppenghui110  -e "show databases;"  >a.txt
[root@iZwz99qnmldt4n744noo9gZ ~]# cat a.txt 
Database
information_schema
blog
mysql
performance_schema
question
wordpress
yii2

参考文档:

www.9778.com,实践出真知

[[email protected] ~]# mysql -e "show databases;" -uroot -ppenghui110 >test.txt
[[email protected] ~]# ls
databases_backup_20170411172702  lnmp1.3-full  lnmp-install.log  test.txt
[[email protected] ~]# cat test.txt 
Database
information_schema
blog
mysql
performance_schema
question
wordpress
yii2
[[email protected] ~]# mysql -uroot -ppenghui110  -e "show databases;"  >a.txt
[[email protected] ~]# cat a.txt 
Database
information_schema
blog
mysql
performance_schema
question
wordpress
yii2

参考文档:

使用mysql的-e参数可以执行各种sql的(创建,删除,增,删,改、查)等各种操作
用法 mysql -uxxx –…