mysql数据库有多种安装方式,本文只介绍在Linux服务器上最实用、最快捷的mysql server安装方法。
(博主在腾讯云服务器上实测CentOS6.5 64位)
一、Linux服务器yum安装(CentOS6.3 64位,CentOS6.5 64位)
所有在服务器上执行的命令,都在 # 后面
1、命令安装mysql
: yum install mysql mysql-server mysql-devel -y
最后提示 Complete! 表示安装成功
2、查看是否生成了mysqld服务, 并设置随机启动
:chkconfig –list |grep mysql
数字代码服务器启动级别,off 代表不随机启动mysqld服务,on代表随机启动服务
我们需要设置mysqld随机启动,执行下面命令进行设置
: chkconfig mysqld on
这样的结果代表正常
:chkconfig –list |grep mysql
如果出现下下图所示,应该是你的系统版本问题(刚开始博主使用的是CentOS7 64位 到这里就不行了,尝试过各种安装方法都装不了,后来重装了CentOS6.5 64位 ok一把就过了)
3、启动mysqld服务
执行如下命令进行启动,两种方法都可以:
:/etc/init.d/mysqld start
:service mysqld start
启动后,ps一下,看下进程是否起来
: ps -ef |grep mysql|grep -v grep
root 1582 1 0 23:26 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 1684 1582 1 23:26 pts/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
根据进程信息可以看到,mysql的数据库data目录是 /var/lib/mysql ,错误日志文件是 /var/log/mysqld.log
查看都有哪些库
: cd /var/lib/mysql
: ls -l
发现有两个库,都是mysql默认自带的,如何手动创建数据库,会在后续的教程中说明。
查看占用端口,默认占用3306端口
: netstat -nutlp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1684/mysqld
4、停止mysqld服务
执行如下命令进行停止,两种方法都可以:
:/etc/init.d/mysqld stop
:service mysqld stop
5、重启mysqld服务
执行如下命令进行重启,两种方法都可以:
: /etc/init.d/mysqld restart
: service mysqld srestart
6、命令行测试连接mysql ,后续可以在命令行中直接管理数据库
直接执行,yum安装的mysql,本地root密码默认为空
: mysql
进入mysql的命令行模式, 代表mysql连接成功,可以看到默认安装的mysql的版本是 5.1.73
关于如何添加mysql用户,及设置密码,请参考后续退出的教程。
二 . 添加mysql用户,及设置密码
1.~
2.设置开机启动
[root@bogon ~]# chkconfig mysqld on
3.启动MySQL服务
[root@bogon ~]# service mysqld start
4.设置MySQL的root用户设置密码
[root@bogon ~]# mysql -u root
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | bogon | |
| root | 127.0.0.1 | |
| | localhost | |
| | bogon | |
+------+-----------+----------+
5 rows in set (0.01 sec)
查询用户的密码,都为空,用下面的命令设置root的密码为root
mysql> set password for root@localhost=password(‘root’);
mysql> exit
6.创建mysql新用户test_user
mysql> create user ‘test_user’@’%’ identified by ‘test_user’;
Query OK, 0 rows affected (0.00 sec)
7.给新用户test_user授权,让他可以从外部登陆和本地登陆
注意:@左边是用户名,右边是域名、IP和%,表示可以访问mysql的域名和IP,%表示外部任何地址都能访问。
mysql> grant all privileges on . to ‘test_user’@’localhost’ identified by ‘test_user’;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on . to ‘test_user’@’%’ identified by ‘test_user’;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+----------+-----------+-------------------------------------------+
| user | host | password |
+----------+-----------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | bogon | |
| root | 127.0.0.1 | |
| | localhost | |
| | bogon | |
| test_user | % | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |
| test_user | localhost | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |
+----------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
8.查看mysql5.1的默认存储引擎
从下面的执行结果可以看出,mysql的默认引擎是MyISAM,这个引擎是不支持事务的。
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
也可以以下面的方式查看
mysql> show variables like ‘storage_engine’;
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)
9.修改mysql的默认引擎为InnoDB
9.1 停止mysql
mysql> exit;
[root@bogon ~]# service mysqld stop
9.2 修改/etc/my.cnf
[mysqld] 后加入
default-storage-engine=InnoDB
加入后my.cnf的内容为:
[root@bogon etc]# more 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-storage-engine=InnoDB
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
9.3 启动mysql
[root@bogon etc]# service mysqld start
Starting mysqld: [ OK ]
9.4 查看mysql默认存储引擎
[root@bogon etc]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
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> show variables like 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
10.CentOS6.5开放mysql端口3306
CentOS6.5默认是不开放端口的,如果要让外部的系统访问CentOS6.5上的mysql,必须开放mysql的端口3306
10.1 修改/etc/sysconfig/iptables
[root@bogon etc]# vim /etc/sysconfig/iptables
(按 i 插入 按键盘上下左右键移动光标 添加下面一行代码 按Esc 再输入:wq保存退出 冒号也要输入)
-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
修改后iptables中的内容是
[root@bogon etc]# more /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
#添加配置项
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
11.重启防火墙
[root@bogon etc]# service iptables restart
这样就可以从外部访问mysql了。
至此,mysql在CentOS6.5上的安装过程、用户创建、外部访问的步骤全部完成。