ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
解决方法:
由于mysql 默认的mysql.sock 是在/var/lib/mysql/mysql.sock,但linux系统总是去/tmp/mysql.sock查找,所以会报错
[root@localhost ~]# find / -name mysql.sock
/var/lib/mysql/mysql.sock
1.直接指定mysql通道
[root@localhost ~]# mysql --socket=/var/lib/mysql/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 2 to server version: 5.0.22
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql>
2. 创建符号连接:
为mysql.sock增加软连接(相当于windows中的快捷方式)。
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
---------------------------------------------------------
下载mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
1、解压复制
tar -zxvf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz
cp -r mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
2、添加系统mysql组和mysql用户
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
3、进入到mysql目录 创建data目录,授权
[root@tyz ~]# cd /usr/local/mysql/
[root@tyz mysql]# mkdir data
[root@tyz mysql]# chown -R mysql:mysql .
4、修改 /etc/my.cnf 如下
[root@tyz mysql]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/usr/local/mysql/data/mysqld.log
pid-file=/usr/local/mysql/dada/mysqld.pid
5、安装
(5.7版本的安装已经不建议使用
mysql_install_db
来安装,而是使用
mysqld
)
[root@tyz mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2018-01-06T22:38:38.836868Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-01-06T22:38:39.248147Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-01-06T22:38:39.318458Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-01-06T22:38:39.440992Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 572a8a86-f332-11e7-bc4b-000c2993b1e7.
2018-01-06T22:38:39.444111Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-01-06T22:38:39.550460Z 1 [Note] A temporary password is generated for root@localhost: 5P,th,b).Yvf
记住这里的临时密码,接下来要用这个密码登录mysql
然后执行以下命令创建RSA private key
[root@tyz mysql]# bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
Generating a 2048 bit RSA private key
................+++
.........................................................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.......................
.........................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.............................+++
............................................+++
writing new private key to 'client-key.pem'
-----
修改当前目录拥有者为root用户 、当前data目录拥有者为mysql用户
[root@tyz mysql]# chown -R root:root ./
[root@tyz mysql]# chown -R mysql:mysql data
6、启动
[root@tyz mysql]# bin/mysqld_safe --user=mysql
执行这一步
这一步,有时候会卡在这里不进入命令行,但实际上这一步就算成功了,crtl+z退出即可
[root@tyz mysql]# ps -ef|grep mysql
root 2192 1871 0 07:00 pts/0 00:00:00 grep mysql
7、登录,输入刚才的临时密码
[root@tyz mysql]# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20
Copyright (c) 2000, 2017, 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.
8、第一次登录需要修改密码
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>quit
9、使用kill结束mysql进程
[root@tyz data]# cat mysqld.pid
2186
[root@tyz data]# kill 2186
10、使用mysql.server 启动 停止
[root@tyz mysql]# ./support-files/mysql.server start
Starting MySQL.[确定]
[root@tyz mysql]# ./support-files/mysql.server stop
Shutting down MySQL..[确定]
12、远程连接
1。 改表法。可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "user" 表里的 "host" 项,从"localhost"改称"%"Sql代码
mysql -u root -pvmwaremysql>use mysql; mysql>update user set host = '%' where user = 'root'; mysql>select host, user from user;
2. 授权法。例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。
Sql代码1. GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITHGRANT OPTION;2.FLUSH PRIVILEGES;如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码Sql代码1. GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY2. 'mypassword' WITH GRANT OPTION;3. FLUSH PRIVILEGES;GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY'mypassword' WITH GRANT OPTION;FLUSH PRIVILEGES;如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库,并使用mypassword作为密码Sql代码1. GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY2. 'mypassword' WITH GRANT OPTION;3. FLUSH PRIVILEGES;GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY'mypassword' WITH GRANT OPTION;FLUSH PRIVILEGES;注意授权后必须FLUSH PRIVILEGES;否则无法立即生效。1045 access denied for user root @192.168.192.12.。。
mysql> update user set password=password("123456") where user="root"; Query OK, 4 rows affected (0.08 sec) Rows matched: 5 Changed: 4 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4. 将mysqld服务加入开机自启动项。
将{mysql}/ support-files/mysql.server 拷贝为/etc/init.d/mysql并设置运行权限,这样就可以使用service mysql命令启动/停止服务,
否则就只能使用{mysql}/bin/mysqld_safe &命令来启动服务
还需要把mysql.server中basedir的相关路径,改为自定义的路径,默认路径是/usr/local/mysql
把mysql注册为开机启动的服务
查看是否添加成功
5.mysql服务的开启和关闭注:在bin/mysqld_safe&这个启动脚本里已默认设置--user=mysql;在脚本末尾加&表示设置此进程为后台进程,区别就是在控制台输入bg,即可将当前进 程转入后台, 当前shell 可进行其他操作。
bin/mysqladmin -uroot -p (注意此时的root是指mysql的root用户)
需要把mysqll加入到环境变量中,或者为mysql建立软链接否则运行mysql命令会出现 -bash: mysql:command not found不加环境变量,就要使用bin/mysql -uroot -p 或者bin/mysqladmin -uroot -p 命令来登录mysql服务
建立软链接:ln -s /Ultrapower/test/mysql/bin/mysql /usr/local/binln -s /Ultrapower/test/mysql/bin/mysqladmin /usr/local/bin
ln -s /Ultrapower/test/mysql/bin/mysqld_safe /usr/local/bin
6.加入环境变量修改/etc/profile文件使其永久性生效,并对所有系统用户生效,在文件末尾加上如下两行代码
PATH=$PATH:/Ultrapower/test/mysql:/Ultrapower/test/mysql/bin
export PATH
最后:执行 命令source /etc/profile或 执行点命令 ./profile使其修改生效,执行完可通过echo $PATH命令查看是否添加成功。
7.登录mysql服务执行:mysql -uroot -p生成的密码
防火墙 添加3306
[root@tyz mysql]# vim /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 8080 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT
[root@tyz mysql]# sudo service iptables restart iptables:将链设置为政策 ACCEPT:filter [确定] iptables:清除防火墙规则:[确定] iptables:正在卸载模块:[确定] iptables:应用防火墙规则:[确定]