一. Mysql安装
mysql安装在linux和windows是不一样的.
- windows: exe安装, 绿色版配置安装
- linux: rpm,tar包,在线安装等
1.1 安装包下载
进入mysql官网社区版本下载页面
选择相应的系统,CentOS选择Red Hat Enterprise Linux / Oracle Linux即可(centos:6.8,选择的redhat linux 6).
Linux-CentOS-6.8-mysql-5.7.19-1.el6.x86_64.rpm-bundle.tar
Windows-64位-mysql-5.7.20-winx64.zip
1.2 linux下安装
1.2.0 删除之前查看
[root@bogon mysql]# find / -name mysql
/usr/lib64/mysql
/usr/share/mysql
/etc/selinux/targeted/active/modules/100/mysql
/root/soft/mysql
[root@bogon mysql]#
1.2.1 删除旧版本数据库
//1.删除老版本的MySQL(本次操作使用的是该方法)
//删除一
rpm -qa | grep -i mysql
yum -y remove mysql-libs*
[root@bogon soft]# rpm -qa | grep -i mysql
mysql-libs-5.1.73-7.el6.x86_64
[root@bogon soft]# yum -y remove mysql-libs*
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.73-7.el6 will be erased
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-6.el6_7.1.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-6.el6_7.1.x86_64
--> Processing Dependency: mysql-libs for package: 2:postfix-2.6.6-6.el6_7.1.x86_64
--> Running transaction check
---> Package postfix.x86_64 2:2.6.6-6.el6_7.1 will be erased
--> Processing Dependency: /usr/sbin/sendmail for package: cronie-1.4.4-15.el6_7.1.x86_64
--> Running transaction check
---> Package cronie.x86_64 0:1.4.4-15.el6_7.1 will be erased
--> Processing Dependency: cronie = 1.4.4-15.el6_7.1 for package: cronie-anacron-1.4.4-15.el6_7.1.x86_64
--> Running transaction check
---> Package cronie-anacron.x86_64 0:1.4.4-15.el6_7.1 will be erased
--> Processing Dependency: /etc/cron.d for package: crontabs-1.10-33.el6.noarch
--> Processing Dependency: /etc/cron.d for package: sysstat-9.0.4-31.el6.x86_64
--> Restarting Dependency Resolution with new changes.
--> Running transaction check
---> Package crontabs.noarch 0:1.10-33.el6 will be erased
---> Package sysstat.x86_64 0:9.0.4-31.el6 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================
Removing:
mysql-libs x86_64 5.1.73-7.el6 @anaconda-CentOS-201605220104.x86_64/6.8 4.0 M
Removing for dependencies:
cronie x86_64 1.4.4-15.el6_7.1 @anaconda-CentOS-201605220104.x86_64/6.8 174 k
cronie-anacron x86_64 1.4.4-15.el6_7.1 @anaconda-CentOS-201605220104.x86_64/6.8 43 k
crontabs noarch 1.10-33.el6 @anaconda-CentOS-201605220104.x86_64/6.8 2.4 k
postfix x86_64 2:2.6.6-6.el6_7.1 @anaconda-CentOS-201605220104.x86_64/6.8 9.7 M
sysstat x86_64 9.0.4-31.el6 @anaconda-CentOS-201605220104.x86_64/6.8 826 k
Transaction Summary
===================================================================================================================================
Remove 6 Package(s)
Installed size: 15 M
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Erasing : sysstat-9.0.4-31.el6.x86_64 1/6
Erasing : crontabs-1.10-33.el6.noarch 2/6
Erasing : cronie-anacron-1.4.4-15.el6_7.1.x86_64 3/6
Erasing : cronie-1.4.4-15.el6_7.1.x86_64 4/6
Erasing : 2:postfix-2.6.6-6.el6_7.1.x86_64 5/6
Erasing : mysql-libs-5.1.73-7.el6.x86_64 6/6
Verifying : cronie-1.4.4-15.el6_7.1.x86_64 1/6
Verifying : mysql-libs-5.1.73-7.el6.x86_64 2/6
Verifying : cronie-anacron-1.4.4-15.el6_7.1.x86_64 3/6
Verifying : crontabs-1.10-33.el6.noarch 4/6
Verifying : sysstat-9.0.4-31.el6.x86_64 5/6
Verifying : 2:postfix-2.6.6-6.el6_7.1.x86_64 6/6
Removed:
mysql-libs.x86_64 0:5.1.73-7.el6
Dependency Removed:
cronie.x86_64 0:1.4.4-15.el6_7.1 cronie-anacron.x86_64 0:1.4.4-15.el6_7.1 crontabs.noarch 0:1.10-33.el6
postfix.x86_64 2:2.6.6-6.el6_7.1 sysstat.x86_64 0:9.0.4-31.el6
Complete!
//删除二
安装新版mysql之前，我们需要将系统自带的mariadb-lib卸载
[root@5201351 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.41-2.el7_0.x86_64
或者试试
rpm -e --nodeps mariadb-libs-1:5.5.52-1.el7.x86_64(以下报错通过该命令执行解决问题)
否则安装会报错
[root@bogon mysql]# rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.52-1.el7.x86_64
file /usr/share/mysql/danish/errmsg.sys from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.52-1.el7.x86_64
file /usr/share/mysql/dutch/errmsg.sys from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.52-1.el7.x86_64
file /usr/share/mysql/english/errmsg.sys from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.52-1.el7.x86_64
file /usr/share/mysql/estonian/errmsg.sys from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.52-1.el7.x86_64
file /usr/share/mysql/french/errmsg.sys from install of mysql-community-common-5.7.20-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.52-1.el7.x86_64
[root@5201351 ~]# rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps
附:卸载mariadb(总有一个适合)
systemctl stop mariadb
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-5.5.52-1.el7.x86_64
rpm -e --nodeps mariadb-server-5.5.52-1.el7.x86_64
rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64
//确定是否都删干净了.
find / -name mysql
1.2.2 tar安装
1.2.2.1 准备安装文件
//1.准备安装文件包
//1.1 新建文件夹保存tar文件
[root@bogon ~]# mkdir soft
[root@bogon ~]# mv mysql-5.7.19-1.el6.x86_64.rpm-bundle.tar soft/
//1.2.解压tar包
[root@bogon soft]# tar -xvf mysql-5.7.19-1.el6.x86_64.rpm-bundle.tar
mysql-community-libs-5.7.19-1.el6.x86_64.rpm #MySQL的库文件
mysql-community-devel-5.7.19-1.el6.x86_64.rpm #MySQL的头文件
mysql-community-embedded-5.7.19-1.el6.x86_64.rpm #MySQL的嵌入式程序
mysql-community-common-5.7.19-1.el6.x86_64.rpm #mysql公用包
mysql-community-libs-compat-5.7.19-1.el6.x86_64.rpm
mysql-community-server-5.7.19-1.el6.x86_64.rpm #MySQL服务端程序
mysql-community-test-5.7.19-1.el6.x86_64.rpm
mysql-community-embedded-devel-5.7.19-1.el6.x86_64.rpm
mysql-community-client-5.7.19-1.el6.x86_64.rpm #MySQL客户端程序
[root@bogon soft]#
进行mysql服务安装,需要以下rpm,按照顺序进行
//1.3 准备以下文件待使用
mysql-community-common-5.7.19-1.el6.x86_64.rpm //mysql公用包
mysql-community-libs-5.7.19-1.el6.x86_64.rpm //MySQL的库文件,依赖于common
mysql-community-client-5.7.19-1.el6.x86_64.rpm //MySQL客户端程序,依赖于libs
mysql-community-server-5.7.19-1.el6.x86_64.rpm //MySQL服务端程序,依赖于client、common
//如果直接安装rpm会报下错:
[root@bogon ~]# rpm -ivh mysql-community-server-5.7.19-1.el6.x86_64.rpm
warning: mysql-community-server-5.7.19-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.19-1.el6.x86_64
mysql-community-common(x86-64) = 5.7.19-1.el6 is needed by mysql-community-server-5.7.19-1.el6.x86_64
[root@bogon ~]#
1.2.2.2 安装数据库
//2.安装数据库
//2.1安装前查看文件效果
[root@bogon soft]# find / -name mysql
[root@bogon soft]#
//2.2 按照顺序按照rpm包
[root@bogon soft]# rpm -ivh mysql-community-common-5.7.19-1.el6.x86_64.rpm
warning: mysql-community-common-5.7.19-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-common ########################################### [100%]
[root@bogon soft]# rpm -ivh mysql-community-libs-5.7.19-1.el6.x86_64.rpm
warning: mysql-community-libs-5.7.19-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-libs ########################################### [100%]
[root@bogon soft]# rpm -ivh mysql-community-client-5.7.19-1.el6.x86_64.rpm
warning: mysql-community-client-5.7.19-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-client ########################################### [100%]
[root@bogon soft]# rpm -ivh mysql-community-server-5.7.19-1.el6.x86_64.rpm
warning: mysql-community-server-5.7.19-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-server ########################################### [100%]
[root@bogon soft]#
//2.3 查看下安装后的效果
[root@bogon soft]# find / -name mysql
/var/lib/mysql
/etc/logrotate.d/mysql
/usr/share/mysql
/usr/lib64/mysql
/usr/bin/mysql
[root@bogon soft]#
安装中的问题
root@bogon mysql]# rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.20-1.el7.x86_64
libnuma.so.1(libnuma_1.1)(64bit) is needed by mysql-community-server-5.7.20-1.el7.x86_64
libnuma.so.1(libnuma_1.2)(64bit) is needed by mysql-community-server-5.7.20-1.el7.x86_64
[root@bogon mysql]# yum install libnuma*
1.2.2.3 初始化数据库
//3.1 初始化数据库
//3.1.1 初始化数据库方法一(本次操作用该种方法,根据提示查看更建议使用方法二)
//3.1.1.1 执行命令
//必须指定datadir,(/usr/bin/mysql_install_db)
//执行后会生成~/.mysql_secret密码文件 (/root/.mysql_secret)
[root@bogon bin]# mysql_install_db --datadir=/var/lib/mysql
2017-10-07 20:36:21 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
//3.1.1.2 查看/etc/my.cnf文件产生了
[root@bogon soft]# ll /etc/my.cnf
-rw-r--r--. 1 root root 960 Jun 22 08:30 /etc/my.cnf
//3.1.2 初始化数据库方法二:
[root@5201351 ~]# mysqld --initialize
//新版的推荐此方法，执行生会在/var/log/mysqld.log生成随机密码
//3.2 赋给mysql用户权限并启动服务
[root@bogon bin]#chown mysql:mysql /var/lib/mysql -R
如果不赋给权限会启动报错.
[root@bogon soft]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]
//3.3 查看安装目录
[root@bogon ~]# cd /var/lib/mysql
[root@bogon mysql]# ll
total 110652
-rw-r-----. 1 mysql mysql 56 Oct 7 20:36 auto.cnf
-rw-------. 1 mysql mysql 1679 Oct 7 20:36 ca-key.pem
-rw-r--r--. 1 mysql mysql 1074 Oct 7 20:36 ca.pem
-rw-r--r--. 1 mysql mysql 1078 Oct 7 20:36 client-cert.pem
-rw-------. 1 mysql mysql 1679 Oct 7 20:36 client-key.pem
-rw-r-----. 1 mysql mysql 420 Oct 7 20:36 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 7 20:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 7 20:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 7 20:36 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 Oct 7 20:36 mysql
drwxr-x---. 2 mysql mysql 4096 Oct 7 20:36 performance_schema
-rw-------. 1 mysql mysql 1679 Oct 7 20:36 private_key.pem
-rw-r--r--. 1 mysql mysql 451 Oct 7 20:36 public_key.pem
-rw-r--r--. 1 mysql mysql 1078 Oct 7 20:36 server-cert.pem
-rw-------. 1 mysql mysql 1675 Oct 7 20:36 server-key.pem
drwxr-x---. 2 mysql mysql 12288 Oct 7 20:36 sys
//3.4 查看/etc/my.cnf
[root@bogon etc]# more /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysq
//3.5 启动msyql服务
[root@bogon etc]# service mysqld start
Starting mysqld: [ OK ]
[root@bogon etc]#
//3.6查看端口
netstat -atln
[root@bogon etc]# netstat -atln
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN
tcp 0 64 192.168.17.129:22 192.168.17.1:52520 ESTABLISHED
tcp 0 0 192.168.17.129:22 192.168.17.1:60942 ESTABLISHED
tcp 0 0 192.168.17.129:22 192.168.17.1:51222 ESTABLISHED
tcp 0 0 192.168.17.129:22 192.168.17.1:52611 ESTABLISHED
tcp 0 0 192.168.17.129:46672 23.33.178.25:80 ESTABLISHED
tcp 0 0 :::3306 :::* LISTEN
tcp 0 0 :::22 :::* LISTEN
tcp 0 0 ::1:631 :::* LISTEN
[root@bogon etc]#
//3306端口已经监听.说明启动成功
1.2.2.4 服务端登录连接查看数据库
//4.1 查看默认密码,准备登陆,并重设密码
root@bogon mysql]# cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2017-10-07 20:36:21
;g(%3xp4eiTX
[root@bogon mysql]#
如果用的是第二种初始化方法,密码位置为
[root@bogon mysql]# more /var/log/mysqld.log
2017-11-22T06:03:58.568894Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-22T06:03:59.827641Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-11-22T06:04:00.103434Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-11-22T06:04:00.216796Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: eefcdbee-cf4a-11e7-
8dc1-5254000cbc62.
2017-11-22T06:04:00.238617Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-11-22T06:04:00.239169Z 1 [Note] A temporary password is generated for root@localhost: Os3aWF-#<1eo
//4.2.1 登陆连接数据库
[root@bogon etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19
Copyright (c) 2000, 2017, 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>
//4.2.2 提示必须要重设密码
mysql> show variables like '%char%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
//4.3 重置用户密码
mysql> alter user 'root'@'localhost' identified by '111111';
Query OK, 0 rows affected (0.00 sec)
mysql>
[root@bogon etc]# service mysqld restart;
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@bogon etc]#
//4.4.1 查看数据库编码
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
发现 character_set_database与character_set_server 使用的是西欧编码
更改下编码准备修改为utf8编码
//4.4.2 修改数据库编码为utf8编码
mysql>
mysql> exit;
Bye
[root@bogon etc]# vim /etc/my.cnf
在my.cnf中的mysqld 中增加
character_set_server=utf8
init_connect='SET NAMES utf8'
追加完如下
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
init_connect='SET NAMES utf8'
~
~
保存, 重启mysql
[root@bogon soft]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@bogon soft]#
//4.4.3 查看修改数据库编码后的编码
[root@bogon etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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 '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> exit;
Bye
//4.5重启mysql服务
[root@bogon etc]# service mysqld restart;
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@bogon etc]#
1.2.2.5 远程登录连接查看数据库
前面的操作此时可以在本机连接了.
下面开启远程连接.
//5.1 连接服务
[root@bogon etc]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
//5.2 使用msyql数据库
mysql> use mysql;
//5.3 查看表结构
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.02 sec)
mysql>
//5.4 查看连接权限表结构
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.02 sec)
//5.5 修改数据开启连接host限制
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//5.6 查看开启后的结果
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
//5.7 刷新权限,这个很关键哟
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)
mysql>
//5.6 开启远程连接防火墙
//防火墙中增加端口允许-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
[root@bogon Desktop]# vi /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 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
//5.8 重启防火墙
[root@bogon Desktop]# 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 ]
[root@bogon Desktop]#
此时也可以通过远程连接数据库服务了.
1.3 windows下安装
安装包下载地址
接下后点击exe执行:
[外链图片转存失败(img-fhEnvZ91-1562426378190)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-1.png)]
[外链图片转存失败(img-XG43EodZ-1562426378191)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-2.png)]
[外链图片转存失败(img-S68LUzt8-1562426378193)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-3.png)]
[外链图片转存失败(img-OuFitBPO-1562426378193)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-4.png)]
[外链图片转存失败(img-irlAKhGm-1562426378194)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-6.png)]
[外链图片转存失败(img-MVSSy32l-1562426378194)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-7.png)]
[外链图片转存失败(img-cQrGaOYZ-1562426378195)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-8.png)]
[外链图片转存失败(img-xmm44r7L-1562426378196)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-9.png)]
[外链图片转存失败(img-BuQX0yMY-1562426378196)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-10.png)]
[外链图片转存失败(img-67ZTKqgN-1562426378196)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-11.png)]
[外链图片转存失败(img-9JKihN5S-1562426378197)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-12.png)]
[外链图片转存失败(img-vLsrS2kZ-1562426378197)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-13.png)]
[外链图片转存失败(img-QkQvWRJH-1562426378197)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-14.png)]
[外链图片转存失败(img-q1gxNAG9-1562426378198)(https://raw.githubusercontent.com/bobshute/public/master/imgs/csdn/Mysql/安装/windows/Mysql-windows安装-15.png)]
(注:以上截图来自网络文档)
一个机器安装2个,需要版本不能一样,并且要配置文件修改my.ini(C:\ProgramData\MySQL\MySQL Server 5.7\my.ini)中的port修改为如3307,mysql_connect也要修改为3307.
2.服务命令总结
2.1 开启/关闭服务
windows服务安装移除
D:\MySQL\mysql-5.7.13\bin>mysqld -remove
Service successfully removed.
D:\MySQL\mysql-5.7.13\bin>mysqld --initialize
D:\MySQL\mysql-5.7.13\bin>mysqld --install
Service successfully installed.
linux
service mysqld start; //启动
service mysqld stop; //关闭
service mysqld restart; //重启
windows-服务开启关闭
Net start 服务名(默认msyql)
Net stop 服务名(默认msyql)
mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57
2.2 数据库连接、建表、建用户、授权
//1.基本操作
mysql -h localhost -P3306 -uroot -p
Enter password:******
mysql>show databases;
mysql>user database_name;
mysql>show tables;
//2.数据库
//创建数据库
mysql>create database database_test;
//使用数据
mysql>user database_test;
//删除数据库
mysql> drop database databaseTest
//3.用户
//创建用户设置密码
mysql>CREATE USER 'usertest'@'localhost' IDENTIFIED BY '111111';
//删除用户
mysql>DROP USER 'usertest'@'localhost';
//设置密码
SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');
//修改当前登录用户密码
SET PASSWORD = PASSWORD("123456");
//4.授权相关
//授权(指定库和表即*.*,*代表全部也可指定库和表,授权是to)
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON databasename.tablename TO 用户名@localhost IDENTIFIED BY '密码';
//授权所有表操作给用户usertest
mysql>GRANT select,insert,update,delete ON * TO 'usertest'@'localhost'
//撤销授权(指定库和表或*.*,*代表全部也可指定库和表,撤销权是from)
REVOKE privilege ON databasename.tablename FROM 数据库名@localhost;
mysql>REVOKE select ON * from 'usertest'@'localhost' ;
//查看权限
SHOW GRANTS FOR 'usertest'@'localhost';
//5.其它
//查看数据库版本
mysql> select version();
//查看数据库时间
mysql> select now();
3.常用的mysql的参数配置
my.cnf
basedir=D:\mysql-5.7.11-winx64
datadir=D:\mysql-5.7.11-winx64\data
port=3306
# 允许最大连接数
max_connections = 200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server = utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
[client]
# 设置mysql客户端默认字符集
default-character-set = utf8
# 区分大小写和不区分大小写(0：大小写敏感；1：大小写不敏感)
lower_case_table_names=1
/*
注意:只能通过配置改变,不能通过sql修改
mysql> set lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
*/
4.bin文件简单解读
D:\MySQL\mysql-5.7.13\bin
共39个文件.其中exe工具35个.还有其它(mysqld.pdb,mysql_config.pl,mysqld_multi.pl,mysqldumpslow.pl)
4.1 mysqld
mysql daemon 在后台运行，默认监听3306端口
mysqld是MySQL的主程序,The MySQL Server
//随机给一个root的密码
mysqld --initialize --console
service mysqld start; //启动
service mysqld stop; //关闭
service mysqld restart; //重启
//–console这个选项是把日志输出到命令行，否则就会把日志输出到datadir目录的xx.err文件中
mysqld --console
4.2 mysql
mysql是MySQL的命令行工具 ; The MySQL Command-Line Tool
//登录
mysql -u root --password=上面分配的随机密码
4.3 mysqladmin
mysqladmin [OPTIONS] command [command-option] command …
command如下:
create databasename 创建一个新数据库
drop databasename 删除一个数据库及其所有表
extended-status 给出服务器的一个扩展状态消息
flush-hosts 洗掉所有缓存的主机
flush-logs 洗掉所有日志
flush-tables 洗掉所有表
flush-privileges 再次装载授权表(同reload)
kill id,id,... 杀死mysql线程
password 新口令，将老口令改为新口令
ping 检查mysqld是否活着
processlist 显示服务其中活跃线程列表
reload 重载授权表
refresh 洗掉所有表并关闭和打开日志文件
shutdown 关掉服务器
status 给出服务器的简短状态消息
variables 打印出可用变量
version 得到服务器的版本信息
4.4 全部列表
共39项
1.echo.exe
2.innochecksum 增强的离线分析工具
my_print_defaults 打印信息到client
my_print_defaults --defaults-file=example.cnf client mysql 将example.cnf中的信息打印到client中。
可以使用my_print_defaults --help查看说明
3.libmecab.dll
4.lz4_decompress.exe 官方链接: https://dev.mysql.com/doc/refman/5.7/en/lz4-decompress.html
5.msvcp120.dll
6.msvcr120.dll
7.my_print_defaults.exe
8.myisam_ftdump.exe
9.myisamchk.exe
myisamchk的功能有点类似“mysql -c/-r”，可以检查和修复MyISAM存储引擎的表，
但只对myisam存储引擎的索引文件有效，不用登陆mysqlserver即可完成操作。
10.myisamlog.exe
11.myisampack.exe
对myisam表进行压缩处理，以缩减占用的存储空间，一般主要用在归档备份的场景下，而且压缩后的myisam表会变成
只读，不能进行任何修改操作。当我们归档备份某些历史数据表，希望该表能够提供高效的查询服务时，就可以通过
myisam工具对myIsam表进行压缩。素虽然更换成archive存储引擎也能够将变成只读的压缩表，但是archive表是不支
持索引的，而压缩后的myisam表任然可以使用其索引。
12.mysql.exe
13.mysql_client_test_embedded.exe
14.mysql_config.pl
15.mysql_config_editor.exe
16.mysql_embedded.exe
17.mysql_plugin.exe
18.mysql_secure_installation.exe
19.mysql_ssl_rsa_setup.exe
20.mysql_tzinfo_to_sql.exe
21.mysql_upgrade.exe
22.mysqladmin.exe
23.mysqlbinlog.exe
mysqlbinlog 程序的主要功能就是分析mysqlserver所产生的二进制日志即binlog，当我们希望通过之前备份的binlog
做一些时间之类的恢复时，mysqlbinlog可以帮助找出恢复操作须要做哪些事情。通过mysqlbinlog，可以把binlog中
指定时间段或指定日志起始和结束位置的内容解释成query语句，并导出到指定的文件中，在解释的过程中，还可以通
过指定数据库名称来过滤输出内容。
24.mysqlcheck.exe
mysqlcheck工具程序可以检查、修复、分析和优化mysql server中的表，但并不是所有的存储引擎都支持这四项功能，
像Innodb就不支持修复功能。实际上，mysqlcheck程序的四项功能都可以通过mysql连接到mysql server之后来实现。
25.mysqld.exe
26.mysqld.pdb
27.mysqld_multi.pl
28.mysqldump.exe
mysqldump 工具其功能就是将mysqlserver中的数据以QUERY语句的形式导出（dump）成文本文件。虽然mysqldump是作
为一种逻辑备份工具为大家所认识，但是称它为QUERY生成导出工具更合适一点，因为通过mysqldump所生成的文件，
全部是QUERY语句，包括数据库和表的创建语句。通过给mysqldump程序加-T选项参数，可以生成其它指定格式的文本
文件，这项功能实际上是调用mysql中的“select * into OUTFILE from ...”语句实现的，也可以通过“-d --no-data”
仅仅生成结构创建的语句，当生成QUERY语句的时候，字符集设置这一项比较关键，建议每次执行Mysqldump程序的时
候都通过尽量做到 “-default-charseter-set=name”显式指定字符集内容，防止以错误的字符集生成不可用的内容，
mysqldump所生成的SQL文件可以通过mysql工具执行。
29.mysqldumpslow.pl
30.mysqlimport.exe
mysqlimport 程序将以特定格式存放的文本数据导入指定的mysqlserver中，比如将一个标准的csv文件导入指定数据库的指定表中。
mysqlimport工具实际上只是“LOAD DATA INFILE” 命令的一个包装实现
31.mysqlpump.exe
32.mysqlshow.exe
33.mysqlslap.exe
34.mysqltest_embedded.exe
35.mysqlxtest.exe
36.perror.exe
37.replace.exe
38.resolveip.exe
39.zlib_decompress.exe
5.常用命令总结
启动数据库
service mysql start
数据备份数据:
mysqldump -h192.168.1.1 -uroot -ppassword1 -P3306 db1 > db1.sql
数据库还原数据:
mysql -h192.168.1.2 -uroot -ppassword2 db1 < db1.sql
扫一扫
484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
