# 对其他远程连接的mysql客户端的配置 [mysql] socket=/var/lib/mysql/mysql.sock # set mysql client default chararter default-character-set=utf8mb4
# 解决 this is incompatible with sql_mode=only_full_group_by 的错误 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 本地mysql服务的配置 [mysqld] socket=/var/lib/mysql/mysql.sock bind-address=0.0.0.0 # set mysql server port port = 3306 #默认是3306，防止端口冲突发生，可以避免使用 3306 mysql默认端口 # set mysql install base dir basedir=/usr/local/mysql # set the data store dir datadir=/usr/local/mysql/data # set the number of allow max connnection # 设置置 MySQL 的最大连接，按你实际情况适当设置。出现 'Too many connections' 错误，是因为 max_connections 的值太低，需要设置更高的链接数， # max_connection 值被设高之后的缺陷是当服务器运行超过设置阈值或更高的活动事务时会变的没有响应。 max_connections=3000 character-set-client-handshake = FALSE # set server charactre default encoding character-set-server=utf8mb4 collation-server = utf8mb4_unicode_ci # the storage engine default-storage-engine=INNODB lower_case_table_names=1 max_allowed_packet=16M explicit_defaults_for_timestamp=true
# 解决 this is incompatible with sql_mode=only_full_group_by 的错误 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@renshelaodongbangong-sj0001 mysql]# /etc/init.d/mysqld restart MySQL server PID file could not be found! [FAILED] Starting MySQL.Logging to '/usr/local/mysql/data/renshelaodongbangong-sj0001.err'. [ OK ]
#set mysql environment export PATH=$PATH:/usr/local/mysql/bin
# 使文件生效 [root@ren mysql]# vim /etc/profile [root@ren mysql]# source /etc/profile
设置密码
1 2 3
# 获取初始密码 cat /root/.mysql_secret ugcNJt;ZAx7+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
# 登录 mysql -uroot -p Enter password: #此处填写上边获取到的初始密码 # 修改 set password=password('password'); UPDATE user SET Password = password('password') WHERE User = 'mysql'; flush privileges exit
# 验证新密码 mysql -uroot -p Enter password: #此处输入新密码
show tables; show databases;
常见报错
（1）如果报错：ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)，
解决方案，创建软链接：
1
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
也可以使用下面的方式连接：
1
mysql -uroot -h 127.0.0.1 -p
（2）ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
使用忽略授权表的方法进入mysql：
1 2 3
vim my.cnf [mysqld] skip-grant-tables=1
使用命令重启mysql:
1 2 3
service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
# 进入mysql mysql -uroot -p
use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> ALTER USER USER() IDENTIFIED BY 'test1'; Query OK, 0 rows affected (0.05 sec)
select * from mysql.user where user='root' \G update user set authentication_string=password('test') where user='root' and Host='localhost';
update user set password_expired='N'where user='root';