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
Enter password:
查看sql scripts：
[root@cent65 mysql]# cat grants.sql
- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.5.46-37.5 at 2015-12-03 12:22:00
-- Grants for ''@'cent65'
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 ''@'%';
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 ''@'%';
GRANT USAGE ON *.* TO ''@'cent65';
-- Grants for ''@'localhost'
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 ''@'%';
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 ''@'%';
GRANT USAGE ON *.* TO ''@'localhost';
-- Grants for 'root'@'127.0.0.1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
-- Grants for 'root'@'::1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
-- Grants for 'root'@'cent65'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'cent65' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'cent65' WITH GRANT OPTION;
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
2)、dump 5.5数据库实例的所有信息(除去mysql, information_schema 和performance_schema数据库)