centos安装mysql

2018年2月20日下午6:35

安装mysql

1	[czh@centos6 vhost]$ sudo yum -y install mysql-server

配置字符集

1	[czh@centos6 vhost]$ sudo vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set=utf8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

设置开机自启：

1	[czh@centos6 vhost]$ sudo chkconfig mysqld on

[czh@centos6 vhost]$ sudo chkconfig --list mysqld
mysqld         	0:关闭	1:关闭	2:启用	3:启用	4:启用	5:启用	6:关闭
``` 


第一次启动mysql

[czh@centos6 vhost]$ sudo service mysqld start
Initializing MySQL database: WARNING: The host ‘centos6.8-linux.shared’ could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables…
180220 18:41:23 [Warning] ‘–default-character-set’ is deprecated and will be removed in a future release. Please use ‘–character-set-server’ instead.
OK
Filling help tables…
180220 18:41:23 [Warning] ‘–default-character-set’ is deprecated and will be removed in a future release. Please use ‘–character-set-server’ instead.
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h centos6.8-linux.shared password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[  OK  ]

Starting mysqld: [ OK ]

1 2	第一次登陆，并删除匿名用户：

[czh@centos6 vhost]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

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.

mysql> delete from mysql.user where user=’’;
Query OK, 2 rows affected (0.00 sec)

1
2
3



配置防火墙：

[czh@centos6 vhost]$ sudo vim /etc/sysconfig/iptables

![](/74E02787-D9CB-4CD1-92A6-774BE5F0FB89.png)


mysql新增用户：

mysql> insert into mysql.user(Host,User,Password)values(“localhost”,”mmall”,password(“mmall”));
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql>



创建数据库：
注：这里的数据库名用``,而不是使用单引号’‘

mysql> create database mmall default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

1
2
3


查看和设置权限：
[mysql权限](bear://x-callback-url/open-note?id=4FE6BA17-41ED-45D6-9335-A994D160CBFF-331-0004AB35243310F3)这里要看这片文章才会明白为啥设计了权限之后，我们的select * from mysql.user \G;之后结果仍然为N

mysql> select * from mysql.user \G;
*** 1. row ***
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*** 2. row ***
Host: centos6.8-linux.shared
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*** 3. row ***
Host: 127.0.0.1
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*** 4. row ***
Host: localhost
User: mmal
Password: *B24D58108523CB417C6988470B0FD4141D7FBAF6
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
4 rows in set (0.00 sec)

ERROR:
No query specified

1
2

mysql> grant all privileges on mmall.* to mmall@’%’ identified by ‘mmall’ with grant option;
Query OK, 0 rows affected (0.00 sec)

1
2
3

注：此时的结果不变

设置root用户密码：

mysql> set password for root@localhost=password(‘root’);
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 127.0.0.1@localhost=password(‘root’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘127.0.0.1@localhost=password(‘root’)’ at line 1
mysql> set password for root@127.0.0.1=password(‘root’);
Query OK, 0 rows affected (0.00 sec)

mysql>

1 2	密码登陆msyql：

[czh@centos6 vhost]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8

1
2


重启mysql

[czh@centos6 vhost]$ sudo service mysqld restart
[sudo] password for czh:
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]



navicat连接mysql：
[MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.10.210’ (111) 解决方法 - 当年亦如是 - 博客园](https://www.cnblogs.com/zihanxing/p/7049244.html)

[czh@localhost bin]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 Source distribution

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.

mysql> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

注：当我将上面的提高的全部更改之后，发现我的navicat坏了，总提示超时，我重新下载了一个就好了。