您的位置:永利集团登录网址 > 计算机教学 > MySQLStudy之--MySQL普通客商无法当地登录

MySQLStudy之--MySQL普通客商无法当地登录

2019-12-09 07:27

MySQLStudy之--MySQL普通用户无法本地登陆

MySQL Study之--MySQL普通用户无法本地登陆
**在安装完成MySQL后,我们通常添加拥有相应权限的普通用户用来访问数据库。在使用用户本地登录数据库的时候,经常会出现怎么登录也无法登录的情况,但是从其它的mysql客户端却可以登录。

故障现象: [[email protected] ~]# mysql -u root -poracle**
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, 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> select version()g
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.25-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

创建用户并授权
mysql> grant all on prod.* to 'rose'@'%' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user,host from user;

+-------+-----------+
| user  | host      |
+-------+-----------+
| jerry | %         |
| rose  | %         |
| tom   | %         |
| tom1  | %         |
| tom2  | %         |
| root  | 127.0.0.1 |
| root  | ::1       |
|       | localhost |
| jerry | localhost |
| root  | localhost |
| scott | localhost |
| tom   | localhost |
|       | mysrv     |
| root  | mysrv     |
+-------+-----------+
14 rows in set (0.00 sec)

用户登陆:
[[email protected] ~]# mysql -u rose -prose
ERROR 1045 (28000): Access denied for user 'rose'@'localhost' (using password: YES) ---登陆失败!
[[email protected] ~]# mysql -u rose -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 22
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, 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 prod;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'prod'--

---在不用密码的情况下可以登陆,但没有权限访问,应该是匿名用户的身份 !
远程登陆: 图片 1

---远程登陆成功!
一、登录后查看mysql.user表的情况

可以看到,我的数据库中有rose用户和匿名用户localhost;
mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

二、在本机用rose用户登录,发现不用密码可以登录;
[[email protected] ~]# mysql -urose -p
Enter password:

mysql> select user(),current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| [email protected] | @localhost |
+----------------+----------------+
1 row in set (0.00 sec)

登录成功了,使用USER()和CURRENT_USER()两个函数查看所使用的用户。
USER()函数返回你在客户端登陆时指定的用户名和主机名。
CURRENT_USER()函数返回的是MySQL使用授权表中的哪个用户来认证你的登录请求。
这里发现,我使用'rose'@'localhost'这个账户登录数据库(因为在本地登陆时没指定主机,默认是以localhost登录),但是数据库使用的是''@'localhost'这个账户来进行登录认证,而''@'localhost'这个匿名用户是没有密码的,因此我输入空密码登录成功了。但是登录后,所对应的用户的匿名用户。

一般在MySQL在安装完毕后,我们使用mysql_install_db这个脚本生成授权表,会默认创建''@'localhost'这个匿名用户。正是因为这个匿名用户,影响了其他用户从本地登录的认证。
那么MySQL是如何进行用户身份认证呢?

一、当用户从客户端请求登陆时,MySQL将授权表中的条目与客户端所提供的条目进行比较,包括用户的用户名,密码和主机。授权表中的Host字段是可以使用通配符作为模式进行匹配的,如test.example.com, %.example.com, %.com和%都可以匹配test.example.com这个主机。授权表中的User字段不允许使用模式匹配,但是可以有一个空字符的用户名代表匿名用户,并且空字符串可以匹配所有的用户名,就像通配符一样。 当user表中的Host和User有多个值可以匹配客户端提供的主机和用户名时,MySQL将user表读入内存,并且按照一定规则排序,按照排序规则读取到的第一个匹配客户端用户名和主机名的条目对客户端进行身份验证。

二、排序规则:对于Host字段,按照匹配的精确程度进行排序,越精确的排序越前,例如当匹配test.example.com这个主机时, %.example.com比%.com更精确,而test.example.com比%.example.com更精确。对于User字段,非空的字符串用户名比空字符串匹配的用户名排序更靠前。 User和Host字段都有多个匹配值,MySQL使用主机名排序最前的条目,在主机名字段相同时再选取用户名排序更前的条目。因此,如果User和Host字段都有多个匹配值,主机名最精确匹配的条目被用户对用户进行认证。

了解了这个认证流程,就知道为什么server登录失败了。
使用GaMe在本机登录数据时,不指定-h参数默认为localhost主机登录,而在MySQL中有两个匹配的条目:'rose'@'%' 和 ''@'localhost'
匿名用户能够匹配的原因上面说过,空字符串可以匹配所有的用户名,就像通配符一样。
根据MySQL认证时的排序规则,第一个条目的用户名排序更前,第二个条目的主机名更精确,排序更前。
而MySQL会优先使用主机名排序第一的条目进行身份认证,因此''@'localhost'被用户对客户端进行认证。因此,只有使用匿名用户的空密码才能登录进数据库。就会出现下面的情况了。
解决的方法:删除匿名用户(仅仅为了安全也有这个必要)
为什么root用户不会受影响,而只有普通用户不能从本地登录?
因为mysql_install_db脚本会在授权表中生成'root'@'localhost'这个账户。同样的,使用root登录MySQL 时,'root'@'localhost'和''@'localhost'都能匹配登录的账户,但是根据排序规则,主机名相同,而用户名非空字符串优先,因此'root'@'localhost'这个条目的排序更靠前。使用root本地登录是不会被匿名用户遮盖。
解决方法:
授权rose用户本地登陆: mysql> grant all on prod.* to 'rose'@'localhost' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)
从本地登陆: [[email protected] ~]# mysql -u rose -prose
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 26
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, 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 prod;
Database changed
mysql> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 10 | tom |
| 20 | jerry |
| 30 | rose |
+------+-------+
3 rows in set (0.00 sec)

---登陆成功!

MySQL Study之--MySQL普通用户无法本地登陆 在安装完成MySQL后,我们通常添加拥有相应权限的普通用户用...

mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database,104442000

1. 问题描述:

在MySQL控制台下创建数据库出现以下信息:

mysql> CREATE DATABASE python;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'python'

2. 解决方法:

执行以下命令进入控制台:

mysql --user=root -p

输入root用户的密码即可进入mysql控制台:

创建数据库:

create database python;

显示所有数据库:

show databases;

如下:

www.linuxidc.com @www.linuxidc.com:~$ mysql --user=root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014,Oracleand/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> create database python;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| python |
| test |
+--------------------+
5 rows in set (0.02 sec)

mysql>

  1. OK, 以上方法不是最好的, 但却是简单可行的,Enjoy it!!!

4、方法四:

这几天用空密码登录mysql后,然后修改mysql默认密码,使用mysql表出现过这个问题,提示:ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'。网上找了一些方法,终于搞定了。

我用的是xampp集成的mysql,之前空密码能登进去phpmyadmin,但怎么也进不去phpmyadmin的系统表

后来解决成功发现是因为mysql数据库的user表里,存在用户名为空的账户即匿名账户,导致登录的时候是虽然用的是root,但实际是匿名登录的,通过错误提示里的''@'localhost'可以看出来。我用方法一解决了问题,

方法一:
在my.ini的[mysqld]字段加入:

skip-grant-tables

重启mysql服务,这时的mysql不需要密码即可登录数据库
然后进入mysql

mysql>use mysql;
mysql>update user set password=password('新密码') WHERE User='root';
mysql>flush privileges;

运行之后最后去掉my.ini中的skip-grant-tables,重启mysqld即可。

修改mysql密码方法二:
不使用修改my.ini重启服务的方法,通过非服务方式加skip-grant-tables运行mysql来修改mysql密码
停止mysql服务
打开命令行窗口,在bin目录下使用mysqld-nt.exe启动,即在命令行窗口执行: mysqld-nt --skip-grant-tables
然后另外打开一个命令行窗口,登录mysql,此时无需输入mysql密码即可进入。
按以上方法修改好密码后,关闭命令行运行mysql的那个窗口,此时即关闭了mysql,如果发现mysql仍在运行的话可以结束掉对应进程来关闭。
启动mysql服务。

linux下的处理方法:

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql'
mysql> exit
Bye
[[email protected] ~]# service mysqld stop 
Stopping mysqld:                      [ OK ]
[[email protected] ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 

[[email protected] ~]# mysql -u root -p -hlocalhost
Enter password: 

mysql> use mysql

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;

mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

mysql> FLUSH PRIVILEGES;

mysql> GRANT ALL ON *.* TO 'root'@'localhost';

mysql> GRANT ALL ON *.* TO 'root'@'cn.cn.cn.cn';

mysql> GRANT ALL ON *.* TO 'root'@'245.245.245.245';

mysql> GRANT ALL ON *.* TO 'root'@'127.0.0.1';

mysql> FLUSH PRIVILEGES;


mysql> quit
Bye
[[email protected] ~]# service mysqld start 

restart Linux/OS 

ERROR 1044 (42000): Access denied for user ''@'localhost' to database,104442000 1. 问题描述: 在MySQL控制台下创建数据库出现以下信息: mysql CREATE DAT...

MySQL Study之--Percona server 5.5升级5.6

系统环境:

 

操作系统:CentOS_6.5(64)      

 

MySQL:   Percona server 5.5(5.6)

 

一、升级的目的

     

能方面的考量, 修复的bug. 但是在没有充分的测试以前就应用到你的应用中是非常危险的, 因为升级可以能会让你的应用不能正常运作- 也可能引起性能的问题. 此外, 我建议你关注MySQL的发布信息和Percona Server - 看看最近的版本有什么变化. 也许在在最新的版本中已修复了某个你正在烦恼的问题.

 

二、升级的方式   

 

通常情况下,有两中升级方式:

 

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

 

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

 

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

 

理论上讲,最安全的方式是:

 

导出所有用户的权限

 

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

 

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

 

三、升级的步骤

 

1、安装percona server 5.5

[[email protected] 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

[[email protected] ~]# cd /home/mysql/percona-55/

[[email protected] 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

 

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

[[email protected] 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:

[[email protected] percona-55]# rpm -e mysql-server --nodeps

[[email protected] percona-55]# rpm -e mysql --nodeps

[[email protected] percona-55]# rpm -e mysql-devel --nodeps

安装percona server:

 

[[email protected] 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
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%]

 

----安装成功 !

 

2、启动mysql server

[[email protected] percona-55]# service mysql start

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

[[email protected] percona-55]# netstat -an |grep :3306

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN 

 

修改用户口令:

[[email protected] 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:

[[email protected] 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 empG

*************************** 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软件:

[[email protected] yum.repos.d]# yum install -y perl-IO-Socket-SSL* --enablerepo=c6-media

[[email protected] yum.repos.d]# yum install -y perl-TermReadKey*  --enablerepo=c6-media

[[email protected] 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) 获取用户和权限信息. 该操作会备份所有用户的权限

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

Enter password: 

 

查看sql scripts:

[[email protected] 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数据库)

[[email protected] 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

Enter password: 

 

查看sql scripts:

[[email protected] mysql]# cat dbs-to-dump.sql 

prod

test

[[email protected] 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: 

[[email protected] 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 @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;
-- 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 [email protected]_TIME_ZONE */;
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;
-- Dump completed on 2015-12-03 12:31:52

 

3)、停止数据库

[[email protected] mysql]# service mysql stop

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

[[email protected] mysql]# mv /var/lib/mysql /var/lib/mysql55

 

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

[[email protected] mysql]#mv /var/lib/mysql /var/lib/mysql55

[[email protected] mysql]#mkdir /var/lib/mysql

[[email protected] mysql]#chown -R mysql:mysql /var/lib/mysql

三、安装Percona Server 5.6

[[email protected] 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的安装需要强制安装:

[[email protected] 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%]

 

[[email protected] 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%]

 

[[email protected] 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

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

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

 

[[email protected] 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

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

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

 

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

 

1)、卸载server 5.5

[[email protected] percona-56]# rpm -qa |grep Percona-Server-server

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

[[email protected] 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

[[email protected] percona-56]# rpm -e Percona-Server-server-55-5.5.46-rel37.5.el6.x86_64 --nodeps

 

2)、安装server 5.6

[[email protected] 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

 

------------至此,Percona Server 5.6安装成功 !

 

四、将数据import到server 5.6

 

1、启动mysql server

[[email protected] percona-56]# service mysql start

Starting MySQL (Percona Server).[  OK  ]

[[email protected] percona-56]# netstat -an|grep :3306

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

 

2、导入用户权限表

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

Enter password: 

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

 

[[email protected] percona-56]# mysqladmin -u root password "oracle"

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

 

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

Enter password: 

 

3、导入备份数据

[[email protected] percona-56]# mysql -uroot -p -e "SET GLOBAL max_allowed_packet=1024*1024*1024"

Enter password: 

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

Enter password: 

[[email protected] percona-56]# 

 

5、连接server,验证数据

[[email protected] 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)

 

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

 

此时, 所有的表都在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即可.

Study之--Percona server 5.5升级5.6 系统环境: 操作系统:CentOS_6.5(64) MySQL: Percona server 5.5(5.6) 一、升级的目的 能方面的考量, 修复的bug. 但是...

本文由永利集团登录网址发布于计算机教学,转载请注明出处:MySQLStudy之--MySQL普通客商无法当地登录

关键词:

  • 上一篇:没有了
  • 下一篇:没有了