Linux下Mysql使用问题总结

1、找不到mysql服务

mysqld: unrecognized service

解决办法

yum install mysql-server

2、报错：java.sql.SQLException: null,  message from server: "Host 'S1PA124' is not allowed to connect to this MySQL server"

解决办法

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

3、修改mysql密码

#/etc/init.d/mysql stop
#mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
#mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/init.d/mysql restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>

4、mysql需要安装以下几个服务

yum install mysql 
yum install mysql-server 
yum install mysql-devel

5、Mysql刚安装好默认密码为空

[root@fk01 ~]# mysql -uroot -p //回车,再回车

参考:http://youyizhimen.blog.163.com/blog/static/17091726720114654326691/

6、修改mysql的dataDir

（1）停止mysql：service mysqld stop

（2）创建转移动的数据目录：mkdir -p /app/mysql/data

（3）将mysql默认的数据目录移到新目录下：mv /var/lib/mysql /app/mysql/data

（4）创建软链接：ln -s /app/mysql/data/mysql /var/lib/mysql

（5）修改/app/mysql的权限，即是把该目录的权限给mysql用户：chown -R /app/mysql

（6）启动mysql服务：service mysqld restart

（7）错误总结：

[root@fk01 var]# service mysqld restart
Stopping MySQL:  [FAILED]
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:  [FAILED]

查看mysql的日志

[root@fk01 log]# tail -200 mysqld.log
150304 14:13:40  mysqld started
150304 14:13:40  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
150304 14:13:40  mysqld ended

从上面可以看出与mysql用户的权限有关，解决办法： chown -R /app/mysql

7、复制mysql的数据库到另一台机器上

源数据库名：source_db   用户名：root 密码：123456
目标数据库名：target_db   用户名：root 密码：123456  所在主机：192.168.1.100

在目标数据库授权给源机器的访问权限

grant all privileges on target_db.* to root@192.168.1.101  identified by '123456';

进到源数据库的数据目录执行

mysqldump source_db -uroot -p123456 --opt | mysql target_db -uroot -p123456 -h 192.168.1.1