通常情况下,有两中升级方式:
直接升级:安装好新版本数据库后,利用已经存在的数据文件夹,同时运行mysql_upgrade脚本来升级。
SQL 导出: 从一个较老版本的mysql把数据导出,然后恢复到新版本的数据库中。(利用mysqldump工具)。
相比之下,第二种方式更安全些,但是这也会使得升级的过程要慢一些。
理论上讲,最安全的方式是:
导出所有用户的权限
导出所有数据并恢复到新版本数据库中
恢复用户权限到新数据库中
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
- Percona-Server-55-debuginfo-5.5.46-rel37.5.el6.x86_64.rpm
- Percona-Server-client-55-5.5.46-rel37.5.el6.x86_64.rpm
- Percona-Server-devel-55-5.5.46-rel37.5.el6.x86_64.rpm
- Percona-Server-server-55-5.5.46-rel37.5.el6.x86_64.rpm
- Percona-Server-shared-55-5.5.46-rel37.5.el6.x86_64.rpm
- Percona-Server-test-55-5.5.46-rel37.5.el6.x86_64.rpm
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
[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
warning: Percona-Server-55-debuginfo-5.5.46-rel37.5.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-shared-5########################################### [ 17%]
2:Percona-Server-client-5########################################### [ 33%]
3:Percona-Server-server-5########################################### [ 50%]
- 151203 10:57:15 [Note] /usr/sbin/mysqld (mysqld 5.5.46-37.5) starting as process 2537 ...
- 151203 10:57:27 [Note] /usr/sbin/mysqld (mysqld 5.5.46-37.5) starting as process 2546 ...
- PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
- To do so, start the server, then issue the following commands:
- /usr/bin/mysqladmin -u root password 'new-password'
- /usr/bin/mysqladmin -u root -h cent65 password 'new-password'
- Alternatively you can run:
- /usr/bin/mysql_secure_installation
- which will also give you the option of removing the test
- databases and anonymous user created by default. This is
- strongly recommended for production servers.
- See the manual for more instructions.
- Please report any problems at
- https://bugs.launchpad.net/percona-server/+filebug
- Percona recommends that all production deployments be protected with a support
- contract (http://www.percona.com/mysql-suppport/) to ensure the highest uptime,
- be eligible for hot fixes, and boost your team's productivity.
- Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
- Run the following commands to create these functions:
- mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
- mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
- mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
- See http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details
- 4:Percona-Server-test-55 ########################################### [ 67%]
- 5:Percona-Server-devel-55########################################### [ 83%]
- 6:Percona-Server-55-debug########################################### [100%]
[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)'
[root@cent65 percona-55]# mysql -u root -p
Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 6
- Server version: 5.5.46-37.5 Percona Server (GPL), Release 37.5, Revision 684ce54
- Copyright (c) 2009-2015 Percona LLC and/or its affiliates
- Copyright (c) 2000, 2015, 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 |
| 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)
warning: percona-toolkit-2.2.10-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:percona-toolkit ########################################### [100%]
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;
Enter password:
[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
Enter password:
[root@cent65 mysql]# cat full-data-dump.sql
- -- MySQL dump 10.13 Distrib 5.5.46-37.5, for Linux (x86_64)
- --
- -- Host: localhost Database: prod
- -- ------------------------------------------------------
- -- Server version 5.5.46-37.5
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
- /*!40101 SET NAMES utf8 */;
- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
- /*!40103 SET TIME_ZONE='+00:00' */;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
- -- Current Database: `prod`
- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `prod` /*!40100 DEFAULT CHARACTER SET latin1 */;
- USE `prod`;
- -- Table structure for table `emp`
- DROP TABLE IF EXISTS `emp`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `emp` (
- `id` int(11) DEFAULT NULL,
- `name` varchar(10) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- /*!40101 SET character_set_client = @saved_cs_client */;
- -- Dumping data for table `emp`
- LOCK TABLES `emp` WRITE;
- /*!40000 ALTER TABLE `emp` DISABLE KEYS */;
- INSERT INTO `emp` VALUES (10,'tom'),(20,'jerry'),(30,'rose');
- /*!40000 ALTER TABLE `emp` ENABLE KEYS */;
- UNLOCK TABLES;
- -- Dumping events for database 'prod'
- -- Dumping routines for database 'prod'
- -- Current Database: `test`
- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
- USE `test`;
- -- Dumping events for database 'test'
- -- Dumping routines for database 'test'
- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
- /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
- /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
- /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
- /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
- /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
- /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
- -- Dump completed on 2015-12-03 12:31:52
Shutting down MySQL (Percona Server).......[ OK ]
[root@cent65 mysql]# mv /var/lib/mysql /var/lib/mysql55
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
[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
Preparing... ########################################### [100%]
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
Preparing... ########################################### [100%]
1:Percona-Server-client-5########################################### [100%]
warning: Percona-Server-test-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-test-56 ########################################### [100%]
warning: Percona-Server-devel-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-devel-56########################################### [100%]
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
error: Failed dependencies:
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
[root@cent65 percona-56]# rpm -ivh Percona-Server-server* --force --nodeps
warning: Percona-Server-server-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-server-5########################################### [100%]
- 2015-12-03 12:41:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
- 2015-12-03 12:41:04 0 [Note] /usr/sbin/mysqld (mysqld 5.6.25-73.1) starting as process 4000 ...
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Using atomics to ref count buffer pool pages
- 2015-12-03 12:41:04 4000 [Note] InnoDB: The InnoDB memory heap is disabled
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Memory barrier is not used
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Compressed tables use zlib 1.2.3
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Using Linux native AIO
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Using CPU crc32 instructions
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Initializing buffer pool, size = 128.0M
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Completed initialization of buffer pool
- 2015-12-03 12:41:04 4000 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Database physically writes the file full: wait...
- 2015-12-03 12:41:04 4000 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
- 2015-12-03 12:41:06 4000 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
- 2015-12-03 12:41:08 4000 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
- 2015-12-03 12:41:08 4000 [Warning] InnoDB: New log files created, LSN=45781
- 2015-12-03 12:41:08 4000 [Note] InnoDB: Doublewrite buffer not found: creating new
- 2015-12-03 12:41:09 4000 [Note] InnoDB: Doublewrite buffer created
- 2015-12-03 12:41:09 4000 [Note] InnoDB: 128 rollback segment(s) are active.
- 2015-12-03 12:41:09 4000 [Warning] InnoDB: Creating foreign key constraint system tables.
- 2015-12-03 12:41:09 4000 [Note] InnoDB: Foreign key constraint system tables created
- 2015-12-03 12:41:09 4000 [Note] InnoDB: Creating tablespace and datafile system tables.
- 2015-12-03 12:41:09 4000 [Note] InnoDB: Tablespace and datafile system tables created.
- 2015-12-03 12:41:09 4000 [Note] InnoDB: Waiting for purge to start
- 2015-12-03 12:41:09 4000 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.25-73.1 started; log sequence number 0
- 2015-12-03 12:41:09 4000 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
- 2015-12-03 12:41:09 4000 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
- 2015-12-03 12:41:41 4000 [Note] Binlog end
- 2015-12-03 12:41:41 4000 [Note] InnoDB: FTS optimize thread exiting.
- 2015-12-03 12:41:41 4000 [Note] InnoDB: Starting shutdown...
- 2015-12-03 12:41:43 4000 [Note] InnoDB: Shutdown completed; log sequence number 1625977
- 2015-12-03 12:41:43 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
- 2015-12-03 12:41:43 0 [Note] /usr/sbin/mysqld (mysqld 5.6.25-73.1) starting as process 4025 ...
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Using atomics to ref count buffer pool pages
- 2015-12-03 12:41:43 4025 [Note] InnoDB: The InnoDB memory heap is disabled
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Memory barrier is not used
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Compressed tables use zlib 1.2.3
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Using Linux native AIO
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Using CPU crc32 instructions
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Initializing buffer pool, size = 128.0M
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Completed initialization of buffer pool
- 2015-12-03 12:41:43 4025 [Note] InnoDB: Highest supported file format is Barracuda.
- 2015-12-03 12:41:44 4025 [Note] InnoDB: 128 rollback segment(s) are active.
- 2015-12-03 12:41:44 4025 [Note] InnoDB: Waiting for purge to start
- 2015-12-03 12:41:44 4025 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.25-73.1 started; log sequence number 1625977
- 2015-12-03 12:41:44 4025 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
- 2015-12-03 12:41:44 4025 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
- 2015-12-03 12:41:44 4025 [Note] Binlog end
- 2015-12-03 12:41:44 4025 [Note] InnoDB: FTS optimize thread exiting.
- 2015-12-03 12:41:44 4025 [Note] InnoDB: Starting shutdown...
- 2015-12-03 12:41:46 4025 [Note] InnoDB: Shutdown completed; log sequence number 1625987
- PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
- To do so, start the server, then issue the following commands:
- /usr/bin/mysqladmin -u root password 'new-password'
- /usr/bin/mysqladmin -u root -h cent65 password 'new-password'
- Alternatively you can run:
- /usr/bin/mysql_secure_installation
- which will also give you the option of removing the test
- databases and anonymous user created by default. This is
- strongly recommended for production servers.
- See the manual for more instructions.
- Please report any problems at
- https://bugs.launchpad.net/percona-server/+filebug
- The latest information about Percona Server is available on the web at
- http://www.percona.com/software/percona-server
- Support Percona by buying support at
- http://www.percona.com/products/mysql-support
- WARNING: Default config file /etc/my.cnf exists on the system
- This file will be read by default by the MySQL server
- If you do not want to use this, either remove it, or use the
- --defaults-file argument to mysqld_safe when starting the server
- Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
- Run the following commands to create these functions:
- mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
- mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
- mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
- See http://www.percona.com/doc/percona-server/5.6/management/udf_percona_toolkit.html for more details
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
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Warning: Using a password on the command line interface can be insecure.
Enter password:
3、导入备份数据
[root@cent65 percona-56]# mysql -uroot -p -e "SET GLOBAL max_allowed_packet=1024*1024*1024"
Enter password:
[root@cent65 percona-56]# mysql -uroot -p --max-allowed-packet=1G < /home/mysql/full-data-dump.sql
Enter password:
[root@cent65 percona-56]#
5、连接server,验证数据
[root@cent65 percona-56]# mysql -uroot -p
Enter password:
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
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, 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 |
| mysql |
| performance_schema |
| prod |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use prod;
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> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| emp |
+----------------+
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)