MySQL综合实验：常见操作问题与解决方案

常见问题

• 2202

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/mysql.sock'

方案：

每次启动都会丢失mysql.sock;
根本原因：在于mysql没有在服务器启动时候正常启动；
解决办法：
1、将服务文件拷贝到init.d下，并重命名为mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
2、赋予可执行权限
chmod +x /etc/init.d/mysqld
3、添加服务
chkconfig --add mysqld
4、重启电脑
reboot

• mysqld_safe error

mysqld_safe --defaults-file=/data/multi-mysql/3307/my.cnf 
201017 01:27:41 mysqld_safe error: log-error set to '/data/multi-mysql/3307/mysql-error.log', however file don't exists. Create writable for user 'mysql'.

方案：
创建该文件并修改这个文件的所有者和权限

• 1200

mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

方案：

对3306
mysql> SET GLOBAL server_id=149;
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 149   |
+---------------+-------+

对3307
mysql> SET GLOBAL server_id=150;
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 150   |
+---------------+-------+

注意！！！ 由于“SET GLOBAL server_id=;”命令会在mysql服务重启后丢失，所以一定要写到配置文件里面。
[mysqld]
server_id=149/150

• 2003

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.6.146' (113)
#无法连接到远程数据库？
guest:
1.ping 192.168.6.146 ？可以吗，可以↓
host:
2.mysql -uplrep -u1234567
select user,host,paaaword from mysql.user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root   | lin       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root   | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root   | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|        | localhost |                                           |
|        | lin       |                                           |
| client | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| plrep  | %         | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |
| plrep  | localhost | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |
+--------+-----------+-------------------------------------------+
如果|plrep |% |...|不存在，那么↓
grant all privileges on *.* to 'plrep'@'%' identified by '1234567';
flush privileges;

guest:
3.继续测试，看会不会再报2003错误

• 1045

mysql -uroot -p123456 -h 192.168.6.146 --protocol tcp
Warning: Using a password on the command line interface can be insecure.

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

方案：主服务器关闭防火墙