Linux服务器安装MYSQL数据库
1.检测服务器是否自带MySQL
# rpm -qa | grep mysql
mysql-libs-5.1.73-7.el6.x86_64
有,说明已经自带了
# cat /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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
看安装目录在哪里
# find / -name mysql
/usr/share/mysql
/usr/lib64/mysql
如何使用自带的数据库,未操作
卸载自带的安装包mysql-libs-5.1.73-7.el6.x86_64
# rpm -e mysql-libs-5.1.73-7.el6.x86_64 ---->有依赖
error: Failed dependencies:
libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-6.el6_7.1.x86_64
libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-6.el6_7.1.x86_64
mysql-libs is needed by (installed) postfix-2:2.6.6-6.el6_7.1.x86_64
[root@cloud etc]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps --->强制删除
2.安装MySQL
搜索MySQL
# yum list | grep mysql
apr-util-mysql.x86_64 1.3.9-3.el6_0.1 base
bacula-director-mysql.x86_64 5.0.0-13.el6 base
bacula-storage-mysql.x86_64 5.0.0-13.el6 base
dovecot-mysql.x86_64 1:2.0.9-22.el6 base
freeradius-mysql.x86_64 2.2.6-7.el6_9 updates
libdbi-dbd-mysql.x86_64 0.8.3-5.1.el6 base
mod_auth_mysql.x86_64 1:3.0.0-11.el6_0.1 base
mysql.x86_64 5.1.73-8.el6_8 base
mysql-bench.x86_64 5.1.73-8.el6_8 base
mysql-connector-java.noarch 1:5.1.17-6.el6 base
mysql-connector-odbc.x86_64 5.1.5r1144-7.el6 base
mysql-devel.i686 5.1.73-8.el6_8 base
mysql-devel.x86_64 5.1.73-8.el6_8 base
mysql-embedded.i686 5.1.73-8.el6_8 base
mysql-embedded.x86_64 5.1.73-8.el6_8 base
mysql-embedded-devel.i686 5.1.73-8.el6_8 base
mysql-embedded-devel.x86_64 5.1.73-8.el6_8 base
mysql-libs.i686 5.1.73-8.el6_8 base
mysql-libs.x86_64 5.1.73-8.el6_8 base
mysql-server.x86_64 5.1.73-8.el6_8 base
mysql-test.x86_64 5.1.73-8.el6_8 base
pcp-pmda-mysql.x86_64 3.10.9-9.el6 base
php-mysql.x86_64 5.3.3-49.el6 base
qt-mysql.i686 1:4.6.2-28.el6_5 base
qt-mysql.x86_64 1:4.6.2-28.el6_5 base
rsyslog-mysql.x86_64 5.8.10-10.el6_6 base
rsyslog7-mysql.x86_64 7.4.10-7.el6 base
通过输入 yum install -y mysql-server mysql mysql-devel 命令将mysql mysql-server mysql-devel都安装好(注意:安装mysql时我们并不是安装了mysql客户端就相当于安装好了mysql数据库了，我们还需要安装mysql-server服务端才行)
# yum install -y mysql-server mysql mysql-devel
....
Complete!
等待了一番时间后，yum会帮我们选择好安装mysql数据库所需要的软件以及其它附属的一些软件
查看mysql-server是否安装成功
# rpm -qi mysql-server
Name : mysql-server Relocations: (not relocatable)
Version : 5.1.73 Vendor: CentOS
Release : 8.el6_8 Build Date: Fri 27 Jan 2017 06:25:43 AM HKT
Install Date: Fri 08 Sep 2017 08:06:15 AM HKT Build Host: c1bm.rdu2.centos.org
Group : Applications/Databases Source RPM: mysql-5.1.73-8.el6_8.src.rpm
Size : 25884131 License: GPLv2 with exceptions
Signature : RSA/SHA1, Fri 27 Jan 2017 06:35:28 AM HKT, Key ID 0946fca2c105b9de
Packager : CentOS BuildSystem <http://bugs.centos.org>
URL : http://www.mysql.com
Summary : The MySQL server and related files
Description :
MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
client/server implementation consisting of a server daemon (mysqld)
and many different client programs and libraries. This package contains
the MySQL server and some accompanying files and directories.
安装后会多出一个mysqld的服务
3.启动MYSQL
首次启动提示的信息比较多,有一些初始化信息,下次重启就比较少了
# service mysqld start
Initializing MySQL database: WARNING: The host 'cloud' 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...
OK
Filling help tables...
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 cloud 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 ]
# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3.1启动关闭MySQL
service mysqld start
service mysqld stop
service mysqld restart
4.开机启动MySQL
检查服务是否开机启动,将其设置成开机启动
mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭
# chkconfig --list | grep mysqld
mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off
[root@cloud etc]# chkconfig mysqld on
[root@cloud etc]# chkconfig --list | grep mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
5.初始化配置MySQL账号信息
首次启动的提示信息:
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 cloud password 'new-password'
给我们的root账号设置密码(注意：这个root账号是mysql的root账号，非Linux的root账号)
# /usr/bin/mysqladmin -u root password '密码'
登录MySQL
# mysql -u root -p
Enter password: 密码输入
Welcome to the MySQL
....
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
6.mysql数据库的主要配置文件
/etc/my.cnf 这是mysql的主配置文件
/var/lib/mysql mysql数据库的数据库文件存放位置
/var/log mysql数据库的日志输出存放位置
因为我们的mysql数据库是可以通过网络访问的，并不是一个单机版数据库，其中使用的协议是 tcp/ip 协议，我们都知道mysql数据库绑定的端口号是 3306 ，所以我们可以通过 netstat -anp 命令来查看一下，Linux系统是否在监听 3306 这个端口号：
[root@cloud etc]# netstat -anp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 62925/mysqld
已经监听,结束操作
7.远程连接MySQL数据库
Sequel pro工具连接失败
Connection failed!
Unable to connect to host IP, or the request timed out.
Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).
MySQL said: Can't connect to MySQL server on 'IP' (61)
—>host错误问题
防护墙设置:
# vi /etc/sysconfig/iptables
加入3306端口
-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 8080 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
重启防火墙
# service iptables restart
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
iptables: Applying firewall rules: [ OK ]
测试发现还是不行:
开启MySQL远程访问权限 允许远程连接
1、登录服务器，然后运行命令：mysql -u root –p ，然后输入密码，该步骤是进入数据库。
2、mysql>use mysql;
3、授权：
例如想root使用123456从任何主机连接到mysql服务器：
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
如果想允许用户abc从ip为xx的主机连接到mysql服务器,并使用xxx作为密码：
mysql>GRANT ALL PRIVILEGES ON *.* TO 'abc'@'IP' IDENTIFIED BY '密码' WITH GRANT OPTION;
4、刷新权限： mysql>FLUSH PRIVILEGES;
测试成功!
8.其它
8.1 创建一个新的登录用户
# 其中’%’表示客户端可以为任何ip，当然也可以明确规定客户端的ip地址
# root用户登录
mysql -u root -p
>use mysql
mysql> create database databasename;
mysql> create user 'username'@'%' identified by 'userpassword';
mysql> grant create,drop,select,insert,update,delete on databasename.* to 'username'@'%';
mysql>FLUSH PRIVILEGES;
发现没有数据库game权限,原因是先创建用户再授权,最后再创建数据库.因此授权数据库的时候,数据库必须由有权限的账号已经创建好了才行.—>创建表,移除表,增删改查
8.2 有时候创建了用户确说权限不足
—> todo待深入研究
# 删除存在的空用户
> delete from mysql.user where user = '';
重启登录
# service mysqld restart;
9.Ubuntu上
9.1 配置文件的真正位置
/etc/mysql/mysql.conf.d/mysqld.cnf
10.完全卸载MySQL
10.1 简述
上述方法安装了MySQL之后,版本是5.1版本很低,很多功能使用起来很不方便,而且时间戳的支持也不好,于是打算卸载重新安装更高版本的MySQL.
10.2 卸载方法
]# yum remove mysql mysql-server mysql-libs mysql-server
....
找到残余,删除所有MySQL的文件
[root@VM_0_7_centos ~]# find / -name mysql
/usr/share/mysql
/var/lib/mysql
/var/lib/mysql/mysql
]# rm -rf /usr/share/mysql
]# rm -rf /var/lib/mysql
11.安装MySQL5.7.20
第一步：下载mysql最新版
# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz --no-check-certificate
第二步：在/usr/local/中解压压缩包，并改名为mysql
software]# cd /usr/local/
l]# tar -xzvf /data/software/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
local]# mv mysql-5.7.20-linux-glibc2.12-x86_64/ mysql
第三步：创建用户组mysql，创建用户mysql并将其添加到用户组mysql中，并赋予读写权限
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql mysql/
chgrp -R mysql mysql/
第四步：创建配置文件
vim /etc/my.cnf
#复制以下内容
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#不区分大小写
lower_case_table_names = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=5000
default-time_zone = '+8:00'
按ESC保存并关闭，输入如下命令 :wq!
第五步：初始化数据库
#先安装一下这个东东，要不然初始化有可能会报错
log]# yum install libaio
#手动编辑一下日志文件，什么也不用写，直接保存退出
log]# cd /var/log/
log]# vim mysqld.log
：wq
log]# chmod 777 mysqld.log
log]# chown mysql:mysql mysqld.log
整行执行,等待执行完毕
log]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lc_messages_dir=/usr/local/mysql/share --lc_messages=en_US
第六步：查看初始密码
og]# cat /var/log/mysqld.log
...
0Z 1 [Note] A temporary password is generated for root@localhost: yUe0OqX*eF_V
执行后关注最后一点：root@localhost: 这里就是初始密码
第七步：启动服务，进入mysql，修改初始密码，运行远程连接（这里执行完后，密码将变成：你设置的新密码）
cd /var/run/
mkdir mysqld
chmod 777 mysqld
cd mysqld
vim mysqld.pid
mysqld]# chmod 777 mysqld.pid
]# chown mysql:mysql mysqld.pid
启动MySQL
mysqld]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL SUCCESS!
登录操作
mysqld]# /usr/local/mysql/bin/mysql -u root -p
Enter password: --输入 yUe0OqX*eF_V
修改密码
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
#如果提示必须要修改密码才可以进行操作的话则执行下面操作
set password=password('新密码');
mysql> flush privileges;
mysql> UPDATE `mysql`.`user` SET `Host` = '%', `User` = 'root' WHERE (`Host` = 'localhost') AND (`User` = 'root');
Query OK, 1 row affected (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE `mysql`.`user` SET `Host`='%', `User`='root', `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', `Create_tablespace_priv`='Y', `ssl_type`='', `ssl_cipher`='', `x509_issuer`='', `x509_subject`='', `max_questions`='0', `max_updates`='0', `max_connections`='0', `max_user_connections`='0', `plugin`='mysql_native_password', `authentication_string`='*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9', `password_expired`='N', `password_last_changed`='2017-11-20 12:41:07', `password_lifetime`=NULL, `account_locked`='N' WHERE (`User`='root');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
此时不要退出,如果退出,可能会报错
]# /usr/local/mysql/bin/mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
远程用户建立(可以自动创建新用户并且拥有最高权限,或者修改原账户权限)
grant all privileges on *.* to '新用户名'@'%' identified by '新密码';
flush privileges;
第八步：开机自启
cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
第九步：使用service mysqld命令启动/停止服务
su - mysql
service mysqld start/stop/restart
vim /etc/profile
添加系统路径
export PATH=/usr/local/mysql/bin:$PATH
source /etc/profile
mysql的启动与停止
# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@VM_0_7_centos ~]# service mysqld start
Starting MySQL. SUCCESS!
service mysqld restart
mysql 登录
/usr/local/mysql/bin/mysql -u root -p
