MySQL Study之--Percona server 5.5升级5.6

系统环境：

操作系统：CentOS_6.5(64)

MySQL: Percona server 5.5(5.6)

一、升级的目的

二、升级的方式

通常情况下，有两中升级方式：

直接升级:安装好新版本数据库后，利用已经存在的数据文件夹，同时运行mysql_upgrade脚本来升级。

SQL 导出：从一个较老版本的mysql把数据导出，然后恢复到新版本的数据库中。(利用mysqldump工具)。

相比之下，第二种方式更安全些，但是这也会使得升级的过程要慢一些。

理论上讲，最安全的方式是：

导出所有用户的权限

导出所有数据并恢复到新版本数据库中

恢复用户权限到新数据库中

三、升级的步骤

1、安装percona server 5.5

[root@cent65 percona-55]# uname -a

Linux cent65 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

[root@cent65 ~]# cd /home/mysql/percona-55/

[root@cent65 percona-55]# ls

<code>Percona-Server</code><code>-55</code><code>-debuginfo</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm</code>

<code>Percona-Server-client</code><code>-55</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm</code>

<code>Percona-Server-devel</code><code>-55</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm</code>

<code>Percona-Server-server</code><code>-55</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm</code>

<code>Percona-Server-shared</code><code>-55</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm</code>

<code>Percona-Server-test</code><code>-55</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm</code>

安装percona server与系统自带的mysql冲突，首先卸载系统自带mysql：

[root@cent65 percona-55]# rpm -ivh *

warning: Percona-Server-55-debuginfo-5.5.46-rel37.5.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

error: Failed dependencies:

MySQL conflicts with mysql-5.1.71-1.el6.x86_64

MySQL-server conflicts with mysql-server-5.1.71-1.el6.x86_64

卸载mysql：

[root@cent65 percona-55]# rpm -e mysql-server --nodeps

[root@cent65 percona-55]# rpm -e mysql --nodeps

[root@cent65 percona-55]# rpm -e mysql-devel --nodeps

安装percona server：

<code>[root@cent65 percona</code><code>-55</code><code>]# rpm -ivh *</code>

<code>warning: Percona-Server</code><code>-55</code><code>-debuginfo</code><code>-5.5.</code><code>46</code><code>-rel37</code><code>.5.</code><code>el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY</code>

<code>Preparing... ########################################### [</code><code>100</code><code>%]</code>

<code> </code><code>1</code><code>:Percona-Server-shared</code><code>-5</code><code>########################################### [ </code><code>17</code><code>%]</code>

<code> </code><code>2</code><code>:Percona-Server-client</code><code>-5</code><code>########################################### [ </code><code>33</code><code>%]</code>

<code> </code><code>3</code><code>:Percona-Server-server</code><code>-5</code><code>########################################### [ </code><code>50</code><code>%]</code>

<code>151203</code> <code>10</code><code>:</code><code>57</code><code>:</code><code>15</code> <code>[Note] /usr/sbin/mysqld (mysqld </code><code>5.5.</code><code>46</code><code>-37.5</code><code>) starting </code><code>as</code> <code>process </code><code>2537</code> <code>...</code>

<code>151203</code> <code>10</code><code>:</code><code>57</code><code>:</code><code>27</code> <code>[Note] /usr/sbin/mysqld (mysqld </code><code>5.5.</code><code>46</code><code>-37.5</code><code>) starting </code><code>as</code> <code>process </code><code>2546</code> <code>...</code>

<code>PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !</code>

<code>To do so, start the server, </code><code>then</code> <code>issue the following commands:</code>

<code>/usr/bin/mysqladmin -u root password </code><code>'new-password'</code>

<code>/usr/bin/mysqladmin -u root -h cent65 password </code><code>'new-password'</code>

<code>Alternatively you can run:</code>

<code>/usr/bin/mysql_secure_installation</code>

<code>which will also give you the option of removing the test</code>

<code>databases </code><code>and</code> <code>anonymous user created by default. This is</code>

<code>strongly recommended </code><code>for</code> <code>production servers.</code>

<code>See the manual </code><code>for</code> <code>more instructions.</code>

<code>Please report any problems </code><code>at</code>

<code> </code><code>https:</code><code>//bugs.launchpad.net/percona-server/+filebug</code>

<code>Percona recommends that all production deployments be </code><code>protected</code> <code>with</code> <code>a support</code>

<code>contract (http:</code><code>//www.percona.com/mysql-suppport/) to ensure the highest uptime,</code>

<code>be eligible </code><code>for</code> <code>hot fixes, </code><code>and</code> <code>boost your team's productivity.</code>

<code>Percona Server is distributed </code><code>with</code> <code>several useful UDF (User Defined Function) </code><code>from</code> <code>Percona Toolkit.</code>

<code>Run the following commands to create these functions:</code>

<code>mysql -e </code><code>"CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"</code>

<code>mysql -e </code><code>"CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"</code>

<code>mysql -e </code><code>"CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"</code>

<code>See http:</code><code>//www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details</code>

<code> </code><code>4</code><code>:Percona-Server-test</code><code>-55</code> <code>########################################### [ </code><code>67</code><code>%]</code>

<code> </code><code>5</code><code>:Percona-Server-devel</code><code>-55</code><code>########################################### [ </code><code>83</code><code>%]</code>

<code> </code><code>6</code><code>:Percona-Server</code><code>-55</code><code>-debug########################################### [</code><code>100</code><code>%]</code>

----安装成功！

2、启动mysql server

[root@cent65 percona-55]# service mysql start

Starting MySQL (Percona Server)....[ OK ]

[root@cent65 percona-55]# netstat -an |grep :3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

修改用户口令：

[root@cent65 percona-55]# mysqladmin -u root password "oracle"

mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user 'root'@'localhost' (using password: NO)'

连接mysql server：

[root@cent65 percona-55]# mysql -u root -p

Enter password:

<code>Welcome to the MySQL monitor. Commands end </code><code>with</code> <code>; </code><code>or</code> <code>\g.</code>

<code>Your MySQL connection id is </code><code>6</code>

<code>Server version: </code><code>5.5.</code><code>46</code><code>-37.5</code> <code>Percona Server (GPL), Release </code><code>37.5</code><code>, Revision 684ce54</code>

<code>Copyright (c) </code><code>2009</code><code>-2015</code> <code>Percona LLC </code><code>and</code><code>/</code><code>or</code> <code>its affiliates</code>

<code>Oracle is a registered trademark of Oracle Corporation </code><code>and</code><code>/</code><code>or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners.</code>

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

mysql> show databases;

+--------------------+

| Database |

| information_schema |

| mysql |

| performance_schema |

| test |

4 rows in set (0.00 sec)

创建测试库和表：

mysql> create database prod;

Query OK, 1 row affected (0.00 sec)

mysql> use prod;

Database changed

mysql> create table emp (id int ,name varchar(10));

Query OK, 0 rows affected (0.35 sec)

mysql> insert into emp values (10,'tom');

Query OK, 1 row affected (0.11 sec)

mysql> insert into emp values (20,'jerry');

Query OK, 1 row affected (0.17 sec)

mysql> insert into emp values (30,'rose');

Query OK, 1 row affected (0.05 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

查看表属性：

mysql> show create table emp\G

*************************** 1. row ***************************

Table: emp

Create Table: CREATE TABLE `emp` (

`id` int(11) DEFAULT NULL,

`name` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> select * from emp;

+------+-------+

| id | name |

| 10 | tom |

| 20 | jerry |

| 30 | rose |

3 rows in set (0.00 sec)

二、准备升级percona server5.5到5.6

安装percona toolkit工具：

首先通过yum（本地库）安装perl软件：

[root@cent65 yum.repos.d]# yum install -y perl-IO-Socket-SSL* --enablerepo=c6-media

[root@cent65 yum.repos.d]# yum install -y perl-TermReadKey* --enablerepo=c6-media

[root@cent65 mysql]# rpm -ivh percona-toolkit-2.2.10-1.noarch.rpm

warning: percona-toolkit-2.2.10-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing... ########################################### [100%]

1:percona-toolkit ########################################### [100%]

1) 获取用户和权限信息. 该操作会备份所有用户的权限

[root@cent65 mysql]# pt-show-grants --user=root --ask-pass --flush >/home/mysql/grants.sql

查看sql scripts：

[root@cent65 mysql]# cat grants.sql

<code>- Grants dumped by pt-show-grants</code>

<code>-- Dumped from server Localhost via UNIX socket, MySQL </code><code>5.5</code><code>.</code><code>46</code><code>-</code><code>37.5</code> <code>at </code><code>2015</code><code>-</code><code>12</code><code>-</code><code>03</code> <code>12</code><code>:</code><code>22</code><code>:</code><code>00</code>

<code>-- Grants </code><code>for</code> <code>''</code><code>@</code><code>'cent65'</code>

<code>GRANT ALTER, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test\_%`.* TO </code><code>''</code><code>@</code><code>'%'</code><code>;</code>

<code>GRANT ALTER, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO </code><code>''</code><code>@</code><code>'%'</code><code>;</code>

<code>GRANT USAGE ON *.* TO </code><code>''</code><code>@</code><code>'cent65'</code><code>;</code>

<code>-- Grants </code><code>for</code> <code>''</code><code>@</code><code>'localhost'</code>

<code>GRANT USAGE ON *.* TO </code><code>''</code><code>@</code><code>'localhost'</code><code>;</code>

<code>-- Grants </code><code>for</code> <code>'root'</code><code>@</code><code>'127.0.0.1'</code>

<code>GRANT ALL PRIVILEGES ON *.* TO </code><code>'root'</code><code>@</code><code>'127.0.0.1'</code> <code>WITH GRANT OPTION;</code>

<code>-- Grants </code><code>for</code> <code>'root'</code><code>@</code><code>'::1'</code>

<code>GRANT ALL PRIVILEGES ON *.* TO </code><code>'root'</code><code>@</code><code>'::1'</code> <code>WITH GRANT OPTION;</code>

<code>-- Grants </code><code>for</code> <code>'root'</code><code>@</code><code>'cent65'</code>

<code>GRANT ALL PRIVILEGES ON *.* TO </code><code>'root'</code><code>@</code><code>'cent65'</code> <code>WITH GRANT OPTION;</code>

<code>GRANT PROXY ON </code><code>''</code><code>@</code><code>''</code> <code>TO </code><code>'root'</code><code>@</code><code>'cent65'</code> <code>WITH GRANT OPTION;</code>

<code>-- Grants </code><code>for</code> <code>'root'</code><code>@</code><code>'localhost'</code>

<code>GRANT ALL PRIVILEGES ON *.* TO </code><code>'root'</code><code>@</code><code>'localhost'</code> <code>IDENTIFIED BY PASSWORD </code><code>'*2447D497B9A6A15F2776055CB2D1E9F86758182F'</code> <code>WITH GRANT OPTION;</code>

<code>GRANT PROXY ON </code><code>''</code><code>@</code><code>''</code> <code>TO </code><code>'root'</code><code>@</code><code>'localhost'</code> <code>WITH GRANT OPTION;</code>

<code>FLUSH PRIVILEGES;</code>

2)、dump 5.5数据库实例的所有信息(除去mysql, information_schema 和performance_schema数据库)

[root@cent65 mysql]# mysql -uroot -p -BNe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema')" | tr 'n' ' ' > /home/mysql/dbs-to-dump.sql

[root@cent65 mysql]# cat dbs-to-dump.sql

prod

test

[root@cent65 mysql]# mysqldump -u root -p --routines --events --single-transaction --databases $(cat /home/mysql/dbs-to-dump.sql) > /home/mysql/full-data-dump.sql

[root@cent65 mysql]# cat full-data-dump.sql

<code>-- MySQL dump </code><code>10.13</code> <code>Distrib </code><code>5.5.</code><code>46</code><code>-37.5</code><code>, </code><code>for</code> <code>Linux (x86_64)</code>

<code>-- Host: localhost Database: prod</code>

<code>-- Server version </code><code>5.5.</code><code>46</code><code>-37.5</code>

<code>/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */</code><code>;</code>

<code>/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */</code><code>;</code>

<code>/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */</code><code>;</code>

<code>/*!40101 SET NAMES utf8 */</code><code>;</code>

<code>/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */</code><code>;</code>

<code>/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */</code><code>;</code>

<code>/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */</code><code>;</code>

<code>/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */</code><code>;</code>

<code>-- Current Database: `prod`</code>

<code>CREATE DATABASE </code><code>/*!32312 IF NOT EXISTS*/</code> <code>`prod` </code><code>/*!40100 DEFAULT CHARACTER SET latin1 */</code><code>;</code>

<code>-- Table structure </code><code>for</code> <code>table `emp`</code>

<code>DROP TABLE IF EXISTS `emp`;</code>

<code>/*!40101 SET @saved_cs_client = @@character_set_client */</code><code>;</code>

<code>/*!40101 SET character_set_client = utf8 */</code><code>;</code>

<code>CREATE TABLE `emp` (</code>

<code> </code><code>`id` int(</code><code>11</code><code>) DEFAULT NULL,</code>

<code> </code><code>`name` varchar(</code><code>10</code><code>) DEFAULT NULL</code>

<code>) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code>

<code>/*!40101 SET character_set_client = @saved_cs_client */</code><code>;</code>

<code>-- Dumping data </code><code>for</code> <code>table `emp`</code>

<code>LOCK TABLES `emp` WRITE;</code>

<code>/*!40000 ALTER TABLE `emp` DISABLE KEYS */</code><code>;</code>

<code>INSERT INTO `emp` VALUES (</code><code>10</code><code>,</code><code>'tom'</code><code>),(</code><code>20</code><code>,</code><code>'jerry'</code><code>),(</code><code>30</code><code>,</code><code>'rose'</code><code>);</code>

<code>/*!40000 ALTER TABLE `emp` ENABLE KEYS */</code><code>;</code>

<code>UNLOCK TABLES;</code>

<code>-- Dumping events </code><code>for</code> <code>database </code><code>'prod'</code>

<code>-- Dumping routines </code><code>for</code> <code>database </code><code>'prod'</code>

<code>-- Current Database: `test`</code>

<code>CREATE DATABASE </code><code>/*!32312 IF NOT EXISTS*/</code> <code>`test` </code><code>/*!40100 DEFAULT CHARACTER SET latin1 */</code><code>;</code>

<code>-- Dumping events </code><code>for</code> <code>database </code><code>'test'</code>

<code>-- Dumping routines </code><code>for</code> <code>database </code><code>'test'</code>

<code>/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */</code><code>;</code>

<code>/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */</code><code>;</code>

<code>/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */</code><code>;</code>

<code>/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */</code><code>;</code>

<code>/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */</code><code>;</code>

<code>/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */</code><code>;</code>

<code>-- Dump completed </code><code>on</code> <code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>31</code><code>:</code><code>52</code>

3)、停止数据库

[root@cent65 mysql]# service mysql stop

Shutting down MySQL (Percona Server).......[ OK ]

[root@cent65 mysql]# mv /var/lib/mysql /var/lib/mysql55

4)、移动旧数据库(5.5版本)的数据目录(假设是/var/lib/mysql,此处应该改为你自己的数据目录)

[root@cent65 mysql]#mv /var/lib/mysql /var/lib/mysql55

[root@cent65 mysql]#mkdir /var/lib/mysql

[root@cent65 mysql]#chown -R mysql:mysql /var/lib/mysql

三、安装Percona Server 5.6

[root@cent65 percona-56]# ls -l

total 121104

-rwxr-xr-x. 1 mysql mysql 70389368 Dec 3 11:21 Percona-Server-56-debuginfo-5.6.25-rel73.1.el6.x86_64.rpm

-rwxr-xr-x. 1 mysql mysql 6727084 Dec 3 11:21 Percona-Server-client-56-5.6.25-rel73.1.el6.x86_64.rpm

-rwxr-xr-x. 1 mysql mysql 1031588 Dec 3 11:21 Percona-Server-devel-56-5.6.25-rel73.1.el6.x86_64.rpm

-rwxr-xr-x. 1 mysql mysql 20463420 Dec 3 11:21 Percona-Server-server-56-5.6.25-rel73.1.el6.x86_64.rpm

-rwxr-xr-x. 1 mysql mysql 742496 Dec 3 11:21 Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm

-rwxr-xr-x. 1 mysql mysql 23137340 Dec 3 11:21 Percona-Server-test-56-5.6.25-rel73.1.el6.x86_64.rpm

-rwxr-xr-x. 1 mysql mysql 1508480 Dec 3 11:21 Percona-Server-tokudb-56-5.6.25-rel73.1.el6.x86_64.rpm

Percona server 5.5会与5.6的软件有冲突，所以5.6的安装需要强制安装：

[root@cent65 percona-56]# rpm -ivh Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm --force

warning: Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

1:Percona-Server-shared-5########################################### [100%]

[root@cent65 percona-56]# rpm -ivh Percona-Server-client-56-5.6.25-rel73.1.el6.x86_64.rpm --force --nodeps

warning: Percona-Server-client-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

1:Percona-Server-client-5########################################### [100%]

[root@cent65 percona-56]# rpm -ivh Percona-Server-test-56-5.6.25-rel73.1.el6.x86_64.rpm --force --nodeps

warning: Percona-Server-test-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

1:Percona-Server-test-56 ########################################### [100%]

[root@cent65 percona-56]# rpm -ivh Percona-Server-devel-56-5.6.25-rel73.1.el6.x86_64.rpm --force --nodeps

warning: Percona-Server-devel-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

1:Percona-Server-devel-56########################################### [100%]

server软件的安装需要先卸载server 5.5：

1）、卸载server 5.5

[root@cent65 percona-56]# rpm -qa |grep Percona-Server-server

Percona-Server-server-55-5.5.46-rel37.5.el6.x86_64

[root@cent65 percona-56]# rpm -e Percona-Server-server-55-5.5.46-rel37.5.el6.x86_64

mysql-server is needed by (installed) akonadi-1.2.1-2.el6.x86_64

[root@cent65 percona-56]# rpm -e Percona-Server-server-55-5.5.46-rel37.5.el6.x86_64 --nodeps

2）、安装server 5.6

[root@cent65 percona-56]# rpm -ivh Percona-Server-server* --force --nodeps

<code>warning: Percona-Server-server</code><code>-56</code><code>-5.6.</code><code>25</code><code>-rel73</code><code>.1.</code><code>el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY</code>

<code> </code><code>1</code><code>:Percona-Server-server</code><code>-5</code><code>########################################### [</code><code>100</code><code>%]</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>0</code> <code>[Warning] TIMESTAMP </code><code>with</code> <code>implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation </code><code>for</code> <code>more details).</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>0</code> <code>[Note] /usr/sbin/mysqld (mysqld </code><code>5.6.</code><code>25</code><code>-73.1</code><code>) starting </code><code>as</code> <code>process </code><code>4000</code> <code>...</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>4000</code> <code>[Note] InnoDB: Mutexes </code><code>and</code> <code>rw_locks use GCC atomic builtins</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>4000</code> <code>[Note] InnoDB: Using Linux native AIO</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>4000</code> <code>[Note] InnoDB: Using CPU crc32 instructions</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>4000</code> <code>[Note] InnoDB: Initializing buffer pool, size = </code><code>128.</code><code>0M</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>4000</code> <code>[Note] InnoDB: The </code><code>first</code> <code>specified data file ./ibdata1 did </code><code>not</code> <code>exist: a </code><code>new</code> <code>database to be created!</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>04</code> <code>4000</code> <code>[Note] InnoDB: Setting log file ./ib_logfile101 size to </code><code>48</code> <code>MB</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>06</code> <code>4000</code> <code>[Note] InnoDB: Setting log file ./ib_logfile1 size to </code><code>48</code> <code>MB</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>08</code> <code>4000</code> <code>[Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>08</code> <code>4000</code> <code>[Warning] InnoDB: New log files created, LSN=</code><code>45781</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>08</code> <code>4000</code> <code>[Note] InnoDB: Doublewrite buffer </code><code>not</code> <code>found: creating </code><code>new</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>09</code> <code>4000</code> <code>[Note] InnoDB: Doublewrite buffer created</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>09</code> <code>4000</code> <code>[Warning] InnoDB: Creating foreign key constraint system tables.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>09</code> <code>4000</code> <code>[Note] InnoDB: Percona XtraDB (http:</code><code>//www.percona.com) 5.6.25-73.1 started; log sequence number 0</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>09</code> <code>4000</code> <code>[Note] RSA </code><code>private</code> <code>key file </code><code>not</code> <code>found: /</code><code>var</code><code>/lib/mysql</code><code>//private_key.pem. Some authentication plugins will not work.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>09</code> <code>4000</code> <code>[Note] RSA </code><code>public</code> <code>key file </code><code>not</code> <code>found: /</code><code>var</code><code>/lib/mysql</code><code>//public_key.pem. Some authentication plugins will not work.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>41</code> <code>4000</code> <code>[Note] Binlog end</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>41</code> <code>4000</code> <code>[Note] InnoDB: FTS optimize thread exiting.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>41</code> <code>4000</code> <code>[Note] InnoDB: Starting shutdown...</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4000</code> <code>[Note] InnoDB: Shutdown completed; log sequence number </code><code>1625977</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>0</code> <code>[Warning] TIMESTAMP </code><code>with</code> <code>implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation </code><code>for</code> <code>more details).</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>0</code> <code>[Note] /usr/sbin/mysqld (mysqld </code><code>5.6.</code><code>25</code><code>-73.1</code><code>) starting </code><code>as</code> <code>process </code><code>4025</code> <code>...</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Using atomics to ref count buffer pool pages</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Mutexes </code><code>and</code> <code>rw_locks use GCC atomic builtins</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Memory barrier is </code><code>not</code> <code>used</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Compressed tables use zlib </code><code>1.2.</code><code>3</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Using Linux native AIO</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Using CPU crc32 instructions</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>43</code> <code>4025</code> <code>[Note] InnoDB: Initializing buffer pool, size = </code><code>128.</code><code>0M</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>44</code> <code>4025</code> <code>[Note] InnoDB: Percona XtraDB (http:</code><code>//www.percona.com) 5.6.25-73.1 started; log sequence number 1625977</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>44</code> <code>4025</code> <code>[Note] RSA </code><code>private</code> <code>key file </code><code>not</code> <code>found: /</code><code>var</code><code>/lib/mysql</code><code>//private_key.pem. Some authentication plugins will not work.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>44</code> <code>4025</code> <code>[Note] RSA </code><code>public</code> <code>key file </code><code>not</code> <code>found: /</code><code>var</code><code>/lib/mysql</code><code>//public_key.pem. Some authentication plugins will not work.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>44</code> <code>4025</code> <code>[Note] Binlog end</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>44</code> <code>4025</code> <code>[Note] InnoDB: FTS optimize thread exiting.</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>44</code> <code>4025</code> <code>[Note] InnoDB: Starting shutdown...</code>

<code>2015</code><code>-12</code><code>-03</code> <code>12</code><code>:</code><code>41</code><code>:</code><code>46</code> <code>4025</code> <code>[Note] InnoDB: Shutdown completed; log sequence number </code><code>1625987</code>

<code> </code><code>/usr/bin/mysqladmin -u root password </code><code>'new-password'</code>

<code> </code><code>/usr/bin/mysqladmin -u root -h cent65 password </code><code>'new-password'</code>

<code> </code><code>/usr/bin/mysql_secure_installation</code>

<code> </code><code>https:</code><code>//bugs.launchpad.net/percona-server/+filebug</code>

<code>The latest information about Percona Server is available </code><code>on</code> <code>the web </code><code>at</code>

<code> </code><code>http:</code><code>//www.percona.com/software/percona-server</code>

<code>Support Percona by buying support </code><code>at</code>

<code> </code><code>http:</code><code>//www.percona.com/products/mysql-support</code>

<code>WARNING: Default config file /etc/my.cnf exists </code><code>on</code> <code>the system</code>

<code>This file will be read by default by the MySQL server</code>

<code>If you do </code><code>not</code> <code>want to use </code><code>this</code><code>, either remove it, </code><code>or</code> <code>use the</code>

<code>--defaults-file argument to mysqld_safe when starting the server</code>

<code>See http:</code><code>//www.percona.com/doc/percona-server/5.6/management/udf_percona_toolkit.html for more details</code>

------------至此，Percona Server 5.6安装成功！

四、将数据import到server 5.6

1、启动mysql server

[root@cent65 percona-56]# service mysql start

Starting MySQL (Percona Server).[ OK ]

[root@cent65 percona-56]# netstat -an|grep :3306

tcp 0 0 :::3306 :::* LISTEN

2、导入用户权限表

[root@cent65 percona-56]# mysql -u root -p </home/mysql/grants.sql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@cent65 percona-56]# mysqladmin -u root password "oracle"

Warning: Using a password on the command line interface can be insecure.

3、导入备份数据

[root@cent65 percona-56]# mysql -uroot -p -e "SET GLOBAL max_allowed_packet=1024*1024*1024"

[root@cent65 percona-56]# mysql -uroot -p --max-allowed-packet=1G < /home/mysql/full-data-dump.sql

[root@cent65 percona-56]#

5、连接server，验证数据

[root@cent65 percona-56]# mysql -uroot -p

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f

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.

| prod |

5 rows in set (0.00 sec)

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

mysql> show tables;

+----------------+

| Tables_in_prod |

| emp |

查看表信息：

---------数据库可以正常访问，至此升级结束！

此时, 所有的表都在MySQL 5.6中重建及重新加载完成,所以所有的二进制文件对MySQL 5.6可用. 同时也你完成了最干净/最稳定的升级过程,你可以恢复你的应用- 这个升级过程和valina MySQL与Percona Server的升级过程是一样的.甚至你可以把Oracle MySQL升级到Percona Server. 比如: 把Oracle MySQL 5.5升级到Percona Server 5.6. 再次强调: MySQL的升级过程和Percona Server的升级过程是一样的,只需要将Percona Server 替换成Oracle MySQL即可.

本文转自客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1719303，如需转载请自行联系原作者

MySQL Study之--Percona server 5.5升级5.6

继续阅读

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List