mysql安装配置
1. 检查系统中是否已经安装过mysql
rpm -qa | grep -i mysql
2. 如果存在,则需要删除。
yum -y remove mysql*
如图:
3. 继续检查一下是否还存在mysql
rpm -qa | grep -i mysql
4. 然后下一步就是下载rpm包了。需要下载:
MySQL-client-5.6.15-1.el6.x86_64.rpm
MySQL-devel-5.6.15-1.el6.x86_64.rpm
MySQL-server-5.6.15-1.el6.x86_64.rpm
逐一执行命令:
wget http://cdn.mysql.com/archives/mysql-5.6/MySQL-client-5.6.15-1.el6.x86_64.rpm
wget http://cdn.mysql.com/archives/mysql-5.6/MySQL-devel-5.6.15-1.el6.x86_64.rpm
wget http://cdn.mysql.com/archives/mysql-5.6/MySQL-server-5.6.15-1.el6.x86_64.rpm
5. 接下来就是安装了分别依次执行:
rpm -ivh MySQL-server-5.6.15-1.el6.x86_64.rpm
rpm -ivh MySQL-devel-5.6.15-1.el6.x86_64.rpm
rpm -ivh MySQL-client-5.6.15-1.el6.x86_64.rpm
6. 修改配置文件位置。
cp /usr/share/mysql/my-default.cnf /etc/my.cnf
/usr/bin/mysql_install_db
7. 修改字符集和数据存放目录
vi /etc/my.cnf
更改之后为:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It"s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
port = 3306
default-character-set=utf8
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
datadir = /var/lib/mysql/
port = 3306
# server_id = .....
# socket = .....
lower_case_table_names=1
max_connections=1000
collation_server = utf8_general_ci
character_set_server = utf8
# default-character-set=utf8
# init_connect='SET NAMES utf8'
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
default-character-set = utf8
注意标红的。
然后就是修改数据库的数据目录:
vi /etc/init.d/mysql
修改地址为:/var/lib/mysql/,图片仅供参考
重启数据库:
service mysql restart
大功告成。
8. 启动并修改root密码
cat /root/.mysql_secret
记录最后的密码
启动mysql:/etc/init.d/mysql start
mysql -uroot –p上一步记录的密码,即可登录mysql。
修改新密码:
mysql> set password for 'root'@'localhost' =password('新密码');
设置外部可通过ip访问
mysql>grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
使设置生效
mysql> flush privileges;
退出
exit
设置IP时属性描述
第一个*表示数据库,第二个*表示表。*.*表示所有的库和表
Root表示root用户,password表示root用户的密码
设置自启动
chkconfig mysql on
7. MySQL的默认安装位置
/var/lib/mysql/ #数据库目录
/usr/share/mysql #配置文件目录
/usr/bin #相关命令目录
/etc/init.d/mysql #启动脚本
问题1:
方案一:
当用客户端连接 mysql
时,发现无法连接,看来需要对用户进行重新授权。操作如下:
[[email protected] mysql]# bin/mysql -uroot -p -h 127.0.0.1 -A cws3
Enter password:
Welcome to the MySQL monitor. Commands end with or /g.
Your MySQL connection id is 1863 to server version: 4.1.20-standard
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql> grant ALL PRIVILEGES ON *.* to [email protected]"%" identified by "mysql";
Query OK, 0 rows affected (0.17 sec)
发现这样更改权限以后,远程仍然不能连接,但是用下面的操作就可以了。
mysql> grant ALL PRIVILEGES ON *.* to [email protected]"%" identified by "mysql" WITH GRANT OPTION;
Query OK, 0 rows affected (0.17 sec)
此刻, root 可以被远程连接,当然这里建立其他非root用户也可以远程连接。
方案二:
MySQL 1130错误解决方法:
通过MySQL-Front或MySQL administrator连接MySQL的时候发生的这个错误
ERROR 1130: Host ***.***.***.*** is not allowed to connect to this MySQL server
说明所连接的用户帐号没有远程连接的权限,只能在本机(localhost)登录。
需更改 MySQL 数据库里的 user表里的 host项
把localhost改称%
具体步骤:登陆到MySQL
首先 use MySQL;
按照别人提供的方式update的时候,出现错误。
MySQL> update user set host='%' where user = 'root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
然后查看了下数据库的host信息如下:
MySQL> select host from user where user = 'root';
+-----------------------+
| host |
+-----------------------+
| % |
| 127.0.0.1 |
| localhost.localdomain |
+-----------------------+
3 rows in set (0.00 sec)
host已经有了%这个值,所以直接运行命令:
复制代码 代码如下:
MySQL>flush privileges;