ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
执行如下修改密码命令，依旧报错：
update mysql.user set authentication_string = password ('rootroot') where user= 'root'
alter user 'root'@'localhost' identified by 'rootroot'
grant all privileges on *.* to root@'%' identified by 'rootroot'
flush privileges
需要执行以下命令方可成功：
set password= password('rootroot')
flush privileges
mysql ndbcluster 安装1.数据库规划：
*** 作系统 centos 6.3
ndbcluster 版本：MySQL-Cluster-gpl-7.4.8-1.el6.x86_64
2个管理节点、2个sql节点、2个数据节点（管理节点和sql节点放在同一台服务器上）
如下：
管理节点1 192.168.1.17
管理节点2 192.168.1.18
sql节点1 192.168.1.17
sql节点2 192.168.1.18
ndbd 节点1 192.168.1.19
ndbd 节点2 192.168.1.20
2. *** 作系统设置：
设置服务主机名称
关闭selinux和防火墙
设置内核参数和最大进程数
下载mysql集群
MySQL-Cluster-gpl-7.4.8-1.el6.x86_64.rpm-bundle.tar
上传到服务器的相应目录下如 /home/mysqlinstall
3.安装管理节点和sql节点：
To check if your system has any RPM version of any MySQL package currently installed, run:
shell>rpm -qa | grep -i mysql
检查
[root@redis1 ~]# rpm -qa | grep -i mysql
mysql-libs-5.1.61-4.el6.x86_64
如果有删除
shell>rpm -e mysql-libs-5.1.61-4.el6.x86_64
删除报错：
[root@redis1 ~]# rpm -e mysql-libs-5.1.61-4.el6.x86_64
error: Failed dependencies:
libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
[root@redis1 ~]#
解决：
[root@redis1 ~]# rpm -e --nodeps mysql-libs-5.1.61-4.el6.x86_64
[root@redis1 ~]#
[root@ndbcluster1 mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
[root@ndbcluster1 mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
4.安装数据节点：
执行上面步骤
[root@ndb mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
[root@ndb mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
5.配置集群
管理节点建立目录
mkdir /var/lib/mysql-cluster
mkdir -p /data/mysql/ndbdata
5.启动关闭集群：
启动管理节点
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial (第一次启动加--initial修改了配置文件后的启动)
启动数据节点
ndbmtd --initial
启动sql节点
service mysql start
查看集群状态
shell>ndb_mgm -e "SHOW"
关闭管理节点和数据节点
ndb_mgm -e shutdown
关闭sql节点
service mysql stop
安装遇到的问题：
1.权限问题启动mysql报错
[root@sqlmgm2 mysql]# service mysql start
Starting MySQL..The server quit without updating PID file ([FAILED]sql/sqlmgm2.pid).
[root@sqlmgm2 mysql]#
[root@sqlmgm2 mysql]# ll
total 110644
-rw-rw---- 1 mysql mysql 56 Dec 14 17:13 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Dec 14 17:39 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Dec 14 17:40 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Dec 14 17:13 ib_logfile1
drwx------ 2 mysql mysql 4096 Dec 14 17:39 mysql
drwx------ 2 root root 4096 Dec 14 17:39 ndbinfo
drwx------ 2 root root 4096 Dec 14 17:39 performance_schema
-rw-r----- 1 mysql mysql30068 Dec 14 17:40 sqlmgm2.err
drwx------ 2 root root 4096 Dec 14 17:33 test
[root@sqlmgm2 mysql]# chown mysql:mysql -R mysql
[root@sqlmgm2 mysql]# service mysql start
Starting MySQL.. [ OK ]
[root@sqlmgm2 mysql]#
2.安装rpm包时，报包依赖关系如下：
[root@ndbcluster2 mysqlinstall]# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
libnuma.so.1()(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64
libnuma.so.1(libnuma_1.1)(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64
libnuma.so.1(libnuma_1.2)(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64
解决方法是把安装的依赖的包
[root@ndbcluster2 mysqlinstall]# yum install numactl
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* extras: mirrors.opencas.cn
* updates: mirrors.opencas.cn
Setting up Install Process
Resolving Dependencies
-->Running transaction check
--->Package numactl.x86_64 0:2.0.9-2.el6 will be installed
-->Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================
Package ArchVersion Repository Size
==============================================================================================================================
Installing:
numactl x86_64 2.0.9-2.el6 base 74 k
Transaction Summary
==============================================================================================================================
Install 1 Package(s)
Total download size: 74 k
Installed size: 171 k
Is this ok [y/N]: y
Downloading Packages:
numactl-2.0.9-2.el6.x86_64.rpm | 74 kB 00:00
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Importing GPG key 0xC105B9DE:
Userid : CentOS-6 Key (CentOS 6 Official Signing Key) [email protected]>
Package: centos-release-6-3.el6.centos.9.x86_64 (@anaconda-CentOS-201207061011.x86_64/6.3)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : numactl-2.0.9-2.el6.x86_64 1/1
Verifying : numactl-2.0.9-2.el6.x86_64 1/1
Installed:
numactl.x86_64 0:2.0.9-2.el6
Complete!
3.启动数据库节点无法和管理节点相连
原因由于防火墙和selinux没有关闭
解决关闭防火墙和selinux
一、关闭防火墙
1、重启后永久性生效：
开启：chkconfig iptables on
关闭：chkconfig iptables off
2、即时生效，重启后失效：
开启：service iptables start
关闭：service iptables stop
二、关闭SELinux
vim /etc/selinux/config # 改为 SELINUX=disabled
# 保存退出，重启服务器
init 6
禁用SeLinux
#永久禁用，需要重启生效。
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
# 临时禁用，不需要重启
setenforce 0
4.root用户不能登录
[root@ndbcluster2 ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@ndbcluster2 ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
设置--skip-grant-tables跳过授权表认证
service mysqld stop
在配置文件中添加--skip-grant-tables（/etc/my.cnf）
service mysqld start
另外开个SSH连接
[root@localhost ~]# mysql
mysql>use mysql
mysql>update user set password=password("123456") where user="root"
mysql>flush privileges
mysql>exit
去掉--skip-grant-tables重启mysql,root用户可以用设置的密码登录
设置root用户远程登录
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '123456' WITH GRANT OPTION
执行报如下错误，ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
[root@ndbcluster1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with or \g.
Your MySQL connection id is 2
Server version: 5.6.27-ndb-7.4.8-cluster-gpl
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>
mysql>
mysql>GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'root' WITH GRANT OPTION
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql>GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'root001' WITH GRANT OPTION
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
执行SET PASSWORD修改成功
mysql>SET PASSWORD = PASSWORD('root001')
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'root' WITH GRANT OPTION
Query OK, 0 rows affected (0.02 sec)
mysql>
FLUSH PRIVILEGES
执行完成
远程连接测试，mysql集群节点，发现一个节点可以连接，一个节点不能连接
这个问题纠结很久
原因上面的 *** 作，没有在另外一个节点上执行导致的，mysql集群的用户认证是分开管理的，要两个节点都要执行
索引以后创建用户要两个节点都执行
5.配置两个管理节点时show报错Could not get configuration
[root@ndbcluster2 mysql-cluster]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm>show
Connected to Management Server at: 192.168.1.17:1186
ERROR Message: The cluster configuration is not yet confirmed by all defined management servers. This management server is still waiting for node 6 to connect.
Could not get configuration
* 4012: Failed to get configuration
*The cluster configuration is not yet confirmed by all defined management servers. This management server is still waiting for node 6 to connect.
ndb_mgm>show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.1.18)
id=3 (not connected, accepting connect from 192.168.1.17)
[ndb_mgmd(MGM)] 2 node(s)
[email protected] (mysql-5.6.27 ndb-7.4.8)
[email protected] (mysql-5.6.27 ndb-7.4.8)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.1.17)
id=5 (not connected, accepting connect from 192.168.1.18)
两个管理节点同时都启动起来，才能看到状态
ndb_mgm>show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
[email protected] (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0, *)
[email protected] (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0)
[ndb_mgmd(MGM)] 2 node(s)
[email protected] (mysql-5.6.27 ndb-7.4.8)
[email protected] (mysql-5.6.27 ndb-7.4.8)
[mysqld(API)] 2 node(s)
[email protected] (mysql-5.6.27 ndb-7.4.8)
[email protected] (mysql-5.6.27 ndb-7.4.8)
1、备份数据库，升级MySQL通常不会丢失数据，但保险起见，我们需要做这一步。输入命令：mysqldump -u xxx -h xxx -P 3306 -p --all-databases >databases.sql
2、 停止mysql服务，输入命令：
service mysqld stop
3、 卸载旧版MySQL，输入命令：
yum remove mysql mysql-*
执行过程中会询问你是否移除，此时输入“Y”
查看已安装的软件：rpm -qa¦grep mysql
卸载mysql：yum remove mysql mysql-server mysql-libs compat-mysql51
4、 移除命令执行后，可再看看是否有残余的mysql，输入命令：
yum list installed | grep mysql
如果有，可输入命令删除：
yum remove mysql－libs
5、 下载安装最新的rpm文件。
这步要看你需要什么文件，我安装的是：mysql57-community-release-el6-7.noarch.rpm（如果提前得到了这个文件，第6步可以使用yum localinstall
mysql57-community-release-el6-7.noarch.rpm从本地安装）
rpm -Uvh http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
6、 安装MySQL，输入命令（过程中问询是否yes，一路y下去）：
yum install mysql-community-server
7、 安装完成后，输入命令查看MySQL版本号：
mysql -V
8 、 启动MySQL，输入命令：
service mysqld start
第一次启动比较慢，因为它会自己做初始化，耐心等待即可。
如果启动失败，提示“MySQL Daemon failed to start”，可以尝试先初始化mysql，输入命令：mysqld --initialize。
如果初始化提示：
2015-12-22T07:49:45.386095Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-22T07:49:45.388125Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2015-12-22T07:49:45.388153Z 0 [ERROR] Aborting
说明数据文件夹已被创建，直接删除，让mysql自己创建即可，输入命令：rm -rf /var/lib/mysql
删除后，注意：然后不需要再初始化了，直接启动mysql，它会自己初始化，输入命令：
service mysqld start
9、进入mysql后，第一件事件就是要你改默认密码。mysql 5.7以后，系统自动生成随机的密码，我 *** 作时，没登录上，切换mysql到安全模式启动，自己去修改即可(安全模式修改密码，见另一篇文章：MySQL忘记密码，或：root密码重置报错：mysqladmin: connect to server at 'localhost' failed的解决方案)。
[root@c12 mysql57]# ./bin/mysql -u root -p
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>alter user root identified by 'root'
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
使用mysqladmin总算能改了
[root@c12 mysql57]# ./bin/mysqladmin -u root -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysql>select version()
+-----------+
| version() |
+-----------+
| 5.7.8-rc |
+-----------+
1 row in set (0.00 sec)
10、设置mysql为开机启动
chkconfig --list 显示服务列表
如果看到mysqld的服务，并且3,4,5都是on的话则成功，如果是off，则键入
chkconfig --level 345 mysqld on
reboot重启电脑
netstat -na | grep 3306，如果看到有监听说明服务启动了。也可以用：service mysqld status看状态
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）