整体上，数据库分为两条线：关系型和非关系型
关系型（又叫sql）：mysql，oracle，sqlserver。。。，最典型的特点是支持标准SQL语句
非关系型（又叫nosql）：redis，MongDB
标准的sql语句类似：select * from table;
mysql首先它是开源数据库，可以免费使用。04-05年，去IOE
oracle，24C，使用mysql作为替代
mysql三个阶段：
1.初期开源阶段
2.sun公司阶段
3.oracle阶段
分支演化：percona，mariadb，大部分功能都是完全兼容mysql、
centos7默认安装的mariadb
mysql应用场景：
大规模互联网网站，APP应用等
google，阿里，新浪，腾讯都是主要使用的mysql数据库
mysql内部组成：
mysql-server 服务端
mysql-client 客户端
一：安装部署：
linux下的三种安装方式：
1.yum：首先要配置yum源，yum安装会直接解决包依赖关系
2.rpm：单个软件包的安装.xx.rpm
3.源码安装：make编译方式，部署到linux，可以自定义变量、路径等，源码安装完可以打包成rpm的方式
服务端安装：
第一步：先安装上传工具
yum install lrzsz
安装之后使用rz命令上传文件（或者直接拖拽过去）
第二步：rz----上传MySQL-server-5.6.35-1.el6.x86_64.rpm包
第三步：rpm安装
安装服务端
rpm -ivh MySQL-server-5.6.35-1.el6.x86_64.rpm
安装的注意点：
1.缺什么补什么
yum install numactl.x86_64 -y
2.哪个冲突删哪个
rpm -qa:查询已经安装的rpm包
rpm -qa|grep -i mariadb
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64
卸载系统自带的Mariadb
rpm -qa|grep mariadb //查询出已安装的mariadb
rpm -e --nodeps 文件名 //卸载 ， 文件名为使用rpm -qa|grep mariadb 命令查出的所有文件
报错一：
error: Failed dependencies:
libnuma.so.1()(64bit) is needed by MySQL-server-5.6.35-1.el6.x86_64
解决：
[root@VM_0_7_centos ~]# yum list |grep -i numa
numactl.x86_64 2.0.12-3.el7 os
numactl-devel.i686 2.0.12-3.el7 os
numactl-devel.x86_64 2.0.12-3.el7 os
numactl-libs.i686 2.0.12-3.el7 os
numactl-libs.x86_64 2.0.12-3.el7 os
numad.x86_64 0.5-18.20150602git.el7 os
[root@VM_0_7_centos ~]# yum install numactl.x86_64
报错二：
冲突file /usr/share/mysql/charsets/README from install of MySQL-server-5.6.35-1.el6.x86_64
conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
1.查询冲突的内容
rpm -qa|grep -i mariadb
2。删除冲突的包
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64
报错三：
初始化时没有data:dumper导致报错
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper
yum list |grep -i dumper
yum install perl-Data-Dumper.x86_64 -y
rpm -e MySQL-server-5.6.35-1.el6.x86_64
rm -rf /var/lib/mysql/
客户端安装
第一步：rz----上传MySQL-client-5.6.35-1.el6.x86_64.rpm包
第二步：rpm安装
安装客户端命令 rpm -ivh MySQL-client-5.6.35-1.el6.x86_64.rpm
安装后会有个mysql命令
报错四：
[root@VM_0_7_centos ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
一般是服务没有启动
解决：
1.启动server
service mysql start
Starting MySQL.Logging to '/var/lib/mysql/VM_0_6_centos.err'.
SUCCESS!
2.如何确定服务是否启动成功
service mysql status
ps -ef|grep mysql
报错五：
ERROR 1045 (28000):
Access denied for user 'root'@'localhost' (using password: NO)
需要密码
解决：
初始化密码保存在cat /root/.mysql_secret
mysql -p初始化密码登录数据库
[root@VM_0_7_centos ~]# cat /root/.mysql_secret # The random password set for the root user at Sat Nov 23 10:55:16 2019 (local time): ybGOuPePH09m9bhF # The random password set for the root user at Sat Nov 23 11:24:57 2019 (local time): B57KHGZjQj6smL2v #随机密码有两个应该是安装了2次，以最新的为准 [root@VM_0_7_centos ~]# mysql -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@VM_0_7_centos ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.35 Copyright (c) 2000, 2016, 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>
登录之后：
报错六：
mysql> show databases;#查看当前数据库命令
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
初始化时生成的时随机密码，要求使用前必须自定义设置密码
报错七：
mysql> help SET PASSWORD
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
解决：
#换一种方式修改密码 mysql> set password=password('123456'); Query OK, 0 rows affected (0.00 sec) #修改之后再次查询数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
#退出，用新密码登录 mysql> exit; Bye [root@VM_0_7_centos ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
命令行区分:
mysql> :mysql命令行，sql语句
[root@VM_0_6_centos ~]# ：linux命令行，执行linux命令
二：服务组件，服务目录
[root@VM_0_7_centos ~]# service mysql status SUCCESS! MySQL running (19345) 19345是PID，linux下面叫进程号 #查看进程命令 [root@VM_0_7_centos ~]# ps -ef|grep mysql root 19249 1 0 11:27 pts/5 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/VM_0_7_centos.pid mysql 19 345 19249 0 11:27 pts/5 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/VM_0_7_centos.err --pid-file=/var/lib/mysql/VM_0_7_centos.pid root 22470 18700 0 11:49 pts/5 00:00:00 mysql -px xxxx root 22957 5947 0 11:52 pts/1 00:00:00 grep --color=auto mysql
mysqld_safe：是mysql的守护进程，一般默认都会有，也可以不启动
作用：mysql进程异常挂掉，mysqld_safe会尝试重新拉起一个mysql进程
datadir：数据存储目录
pid-file：进程号文件存储位置
mysql 25039 14984 3 11:57 pts/3 00:00:00/usr/sbin/mysqld
---basedir=/usr #mysql的安装目录，mysql启动命令，服务工具等
---datadir=/var/lib/mysql #mysql的数据存储目录，库表，日志等
---plugin-dir=/usr/lib64/mysql/plugin #插件补丁的目录
---user=mysql #启动用户
---log-error=/var/lib/mysql/VM_0_6_centos.err #错误日志文件
---pid-file=/var/lib/mysql/VM_0_6_centos.pid #进程号存储文件
[root@VM_0_7_centos ~]# netstat -tanlp|grep mysql#查看mysql进程 tcp6 0 0 :::3306 :::* LISTEN 19345/mysqld
三：卸载---后重新安装
标准步骤：
1.停掉mysql
service mysql stop
2.卸载进程
rpm -e MySQL-server-5.6.35-1.el6.x86_64
3.清理垃圾文件
rm -rf /var/lib/mysql/
rm -rf /var/lib/mysql/VM_0_7_centos.pid
[root@VM_0_7_centos ~]# rpm -e MySQL-server-5.6.35-1.el6.x86_64
error: package MySQL-server-5.6.35-1.el6.x86_64 is not installed
#可能是已经被卸载了，可查询已经安装mysql包
rpm -qa|grep -i mysql
[root@VM_0_7_centos ~]# rpm -ivh MySQL-server-5.6.35-1.el6.x86_64.rpm warning: MySQL-server-5.6.35-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] package MySQL-server-5.6.35-1.el6.x86_64 is already installed [root@VM_0_7_centos ~]# rm -rf /var/lib/mysql/ [root@VM_0_7_centos ~]# rpm -ivh MySQL-server-5.6.35-1.el6.x86_64.rpm warning: MySQL-server-5.6.35-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] package MySQL-server-5.6.35-1.el6.x86_64 is already installed [root@VM_0_7_centos ~]# service mysql start Starting MySQL.Logging to '/var/lib/mysql/VM_0_7_centos.err'. 191123 13:48:09 mysqld_safe Directory '/var/lib/mysql' for UNIX socket file don't exists. ERROR! The server quit without updating PID file (/var/lib/mysql/VM_0_7_centos.pid). [root@VM_0_7_centos ~]# rm -rf /var/lib/mysql/^C [root@VM_0_7_centos ~]# rm -rf /var/lib/mysql/VM_0_7_centos.pid [root@VM_0_7_centos ~]# rpm -ivh MySQL-server-5.6.35-1.el6.x86_64.rpm warning: MySQL-server-5.6.35-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:MySQL-server-5.6.35-1.el6 ################################# [100%] 2019-11-23 13:50:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2019-11-23 13:50:02 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2019-11-23 13:50:02 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 6431 ... 2019-11-23 13:50:02 6431 [Note] InnoDB: Using atomics to ref count buffer pool pages 2019-11-23 13:50:02 6431 [Note] InnoDB: The InnoDB memory heap is disabled 2019-11-23 13:50:02 6431 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019-11-23 13:50:02 6431 [Note] InnoDB: Memory barrier is not used 2019-11-23 13:50:02 6431 [Note] InnoDB: Compressed tables use zlib 1.2.3
重新安装----安装上面的步骤再来一遍：
[root@VM_0_7_centos ~]# netstat -tanlp|grep mysql tcp6 0 0 :::6607 :::* LISTEN 9143/mysqld [root@VM_0_7_centos ~]# cd /var/lib/mysql#进入mysql数据存放目录 [root@VM_0_7_centos mysql]# ll total 110624 -rw-rw---- 1 mysql mysql 56 Nov 24 20:33 auto.cnf -rw-rw---- 1 mysql mysql 12582912 Nov 24 20:33 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 24 20:33 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Nov 24 20:26 ib_logfile1 drwx--x--x 2 mysql mysql 4096 Nov 24 20:27 mysql srwxrwxrwx 1 mysql mysql 0 Nov 24 20:33 mysql.sock drwx------ 2 mysql mysql 4096 Nov 24 20:27 performance_schema -rw-r--r-- 1 root root 111 Nov 24 20:27 RPM_UPGRADE_HISTORY -rw-r--r-- 1 mysql mysql 111 Nov 24 20:27 RPM_UPGRADE_MARKER-LAST drwxr-xr-x 2 mysql mysql 4096 Nov 24 20:26 test -rw-rw---- 1 mysql mysql 1749 Nov 24 20:33 VM_0_7_centos.err -rw-rw---- 1 mysql mysql 5 Nov 24 20:33 VM_0_7_centos.pid [root@VM_0_7_centos mysql]# cat auto.cnf [auto] server-uuid=9875938e-0eb6-11ea-8822-525400dd348c [root@VM_0_7_centos mysql]# ps -ef|grep mysql#查看mysql的进程 root 9032 1 0 20:33 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/VM_0_7_centos.pid mysql 9143 9032 0 20:33 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/VM_0_7_centos.err --pid-file=/var/lib/mysql/VM_0_7_centos.pid --port=6607 root 10424 7060 0 20:40 pts/0 00:00:00 grep --color=auto mysql [root@VM_0_7_centos mysql]# cd /usr/share/mysql/#/usr/share/mysql是mysql的安装目录 [root@VM_0_7_centos mysql]# ls binary-configure fill_help_tables.sql my-default.cnf polish bulgarian french mysqld_multi.server portuguese charsets german mysql-log-rotate romanian czech greek mysql_security_commands.sql russian danish hungarian mysql.server SELinux dictionary.txt innodb_memcached_config.sql mysql_system_tables_data.sql serbian dutch italian mysql_system_tables.sql slovak english japanese mysql_test_data_timezone.sql spanish errmsg-utf8.txt korean norwegian swedish estonian magic norwegian-ny ukrainian [root@VM_0_7_centos bin]# pwd /usr/bin [root@VM_0_7_centos bin]# ls -l|grep mysqld -rwxr-xr-x 1 root root 26518 Nov 28 2016 mysqld_multi -rwxr-xr-x 1 root root 26653 Nov 28 2016 mysqld_safe -rwxr-xr-x 1 root root 9101242 Nov 28 2016 mysqldump -rwxr-xr-x 1 root root 7424 Nov 28 2016 mysqldumpslow
MySQL的主要目录
basedir：安装目录cd /usr/share/mysql，/usr/bin
datadir：数据存放目录cd /var/lib/mysql
auto.cnf ：自动生成的，存放的是server_uuid
ibdata1 : 共享表空间，存放元数据和事务日志回滚段的
ib_logfile0 ：事务日志，里面存的是redo_log
ib_logfile1：同上
mysql :这个目录对应的是数据库里的mysql库，
这个库存放的是权限信息和系统元数据信息
mysql.sock ：服务启动生成的套接字，只在本机登陆时有用
performance_schema ：系统库，统计服务运行数据
RPM_UPGRADE_HISTORY ：不是mysql的，是rpm包带的版本记录
RPM_UPGRADE_MARKER-LAST
test ：是mysql初始化时创建的测试库
VM_0_6_centos.err ：mysql的错误日志，对于定位服务异常很重要
VM_0_6_centos.pid ：存放的是服务进程号
重点说明mysql库
登陆mysql ，use mysql 跳转到库
show tables 查看mysql库下的表的列表
[root@VM_0_7_centos mysql]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> 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> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | 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 | +---------------------------+ 28 rows in set (0.00 sec)
对应关系
mysql：server实例-->库-->表（一个表会对应多个物理文件，这里和引擎相关）
三、mysql 的配置文件
默认为：my.cnf
[root@VM_0_7_centos mysql]# find / -name my.cnf
/usr/my.cnf
/etc/my.cnf 为最高优先级
mysql启动时优先去/etc下找配置文件，如果没有会basedir和datadir去找
[root@VM_0_7_centos mysql]# vim /usr/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld]#表示作用范围是server # 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 = /data/mysql_3306/ port = 3309 # server_id = ..... # socket = ..... # 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 ~
[mysqld] 表示，以下参数作用的范围，比如mysqld，那么下面的参数作用范围就是server，如何是[client]那么作用范围就是客户端
#修改端口
-
1.进入my.cnf配置文件
vim /usr/my.cnf
-
2.修改port
port = 3309
-
3.需要重启服务
service mysql restart
-
4，重启之后查看mysql的进程号
netstat -tanlp|grep mysql
[root@VM_0_7_centos mysql]# netstat -tanlp|grep mysql tcp6 0 0 :::3309 :::* LISTEN 14706/mysqld
比如ll，ls等这些命令都是怎么来的呢？
是 有一个可执行文件，默认是放在$PATH下面
[root@VM_0_7_centos mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
比如：
[root@VM_0_7_centos mysql]# find / -name ls /usr/bin/ls #ls在usr/bin下面，只有放在bin下面才是一个可执行的快捷命令，如果移走了就不能执行了
重启报错的问题：
selinux：美国的一个安全部门搞的一套安全组件，必须要求服务走默认端口，也不可以随意修改，修改的话服务会无法正常启动的
解决方法：
1.setenforce 0临时关掉
2.进入vim /etc/selinux/config
修改：
selinux=disabled
彻底关掉
这里引入一个问题：
linux参数修改的加载方式：
修改当前环境变量
修改配置做持久化：修改配置文件重启后生效
mysql参数修改加载方式：
配置文件的修改只有重启才能够重新加载生效
mysql命令行修改，只对当前环境生效的，重启或断开连接都可能失效
有些参数必须修改配置文件重启才能生效
修改datadir ，
为什么要修改呢？因为数据目录往往都会很大，一般都用单独的盘来存储
mkdir /data/mysql_3307
[root@VM_0_7_centos mysql]# mkdir /data/mysql_3307 [root@VM_0_7_centos mysql]# vim /usr/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/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 # These are commonly set, remove the # and set as required. # basedir = ..... datadir = /data/mysql_3307/ port = 3309 # server_id = ..... # socket = ..... # 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 ~ ~ ~ ~ -- INSERT -- [root@VM_0_7_centos mysql]# cd /data/mysql_3307/ [root@VM_0_7_centos mysql_3307]# ll total 0 [root@VM_0_7_centos mysql_3307]# mv /var/lib/mysql/* .#把lib/mysql下面的文件全部移过去 [root@VM_0_7_centos mysql_3307]# ll total 110628 -rw-rw---- 1 mysql mysql 56 Nov 24 20:33 auto.cnf -rw-rw---- 1 mysql mysql 12582912 Nov 24 21:03 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 24 21:03 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Nov 24 20:26 ib_logfile1 drwx--x--x 2 mysql mysql 4096 Nov 24 20:27 mysql srwxrwxrwx 1 mysql mysql 0 Nov 24 21:03 mysql.sock drwx------ 2 mysql mysql 4096 Nov 24 20:27 performance_schema -rw-r--r-- 1 root root 111 Nov 24 20:27 RPM_UPGRADE_HISTORY -rw-r--r-- 1 mysql mysql 111 Nov 24 20:27 RPM_UPGRADE_MARKER-LAST drwxr-xr-x 2 mysql mysql 4096 Nov 24 20:26 test -rw-rw---- 1 mysql mysql 6956 Nov 24 21:03 VM_0_7_centos.err -rw-rw---- 1 mysql mysql 6 Nov 24 21:03 VM_0_7_centos.pid [root@VM_0_7_centos mysql_3307]# service mysql stop#报错了 Shutting down MySQL.. ERROR! The server quit without updating PID file (/data/mysql_3307//VM_0_7_centos.pid). [root@VM_0_7_centos mysql_3307]# ps -ef|grep mysql root 21831 7060 0 21:42 pts/0 00:00:00 grep --color=auto mysql [root@VM_0_7_centos mysql_3307]# service mysql start Starting MySQL SUCCESS! [root@VM_0_7_centos mysql_3307]# ps -ef|grep mysql root 22177 7060 0 21:43 pts/0 00:00:00 grep --color=auto mysql [root@VM_0_7_centos mysql_3307]# ll total 110628 -rw-rw---- 1 mysql mysql 56 Nov 24 20:33 auto.cnf -rw-rw---- 1 mysql mysql 12582912 Nov 24 21:42 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 24 21:43 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Nov 24 20:26 ib_logfile1 drwx--x--x 2 mysql mysql 4096 Nov 24 20:27 mysql srwxrwxrwx 1 mysql mysql 0 Nov 24 21:03 mysql.sock drwx------ 2 mysql mysql 4096 Nov 24 20:27 performance_schema -rw-r--r-- 1 root root 111 Nov 24 20:27 RPM_UPGRADE_HISTORY -rw-r--r-- 1 mysql mysql 111 Nov 24 20:27 RPM_UPGRADE_MARKER-LAST drwxr-xr-x 2 mysql mysql 4096 Nov 24 20:26 test -rw-rw---- 1 mysql mysql 12223 Nov 24 21:43 VM_0_7_centos.err [root@VM_0_7_centos mysql_3307]# netstat -tanlp|grep mysql [root@VM_0_7_centos mysql_3307]# service mysql start #启动失败 Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql_3307//VM_0_7_centos.pid). [root@VM_0_7_centos mysql_3307]#less VM_0_7_centos.err#翻到最后可以看到报错信息，如下截图
报错八：
2019-11-23 14:47:00 17291 [ERROR] /usr/sbin/mysqld: Can't create/write to file '/data/mysql_3306/VM_0_6_centos.pid' (Errc
ode: 13 - Permission denied)
2019-11-23 14:47:00 17291 [ERROR] Can't start server: can't create PID file: Permission denied
这个错误的原因是权限，datadir，mysql必须有读写权限
chown mysql.mysql mysql_3307
[root@VM_0_7_centos mysql_3307]# less VM_0_7_centos.err#查看错误日志，less是翻页查看 [root@VM_0_7_centos mysql_3307]# cd .. [root@VM_0_7_centos data]# ll total 8 drwxr-xr-x 2 mysql mysql 4096 Nov 23 15:25 mysql_3306 drwxr-xr-x 5 root root 4096 Nov 24 21:46 mysql_3307 [root@VM_0_7_centos data]# chown mysql.mysql mysql_3307#赋予它所有用户的权限 [root@VM_0_7_centos data]# service mysql start Starting MySQL. SUCCESS! [root@VM_0_7_centos data]# ps -ef|grep mysql#查看mysql进程的datadir是否改变了 root 23384 1 0 21:49 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/mysql_3307/ --pid-file=/data/mysql_3307//VM_0_7_centos.pid mysql 23508 23384 0 21:49 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql_3307/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/mysql_3307//VM_0_7_centos.err --pid-file=/data/mysql_3307//VM_0_7_centos.pid --port=3309 root 24230 7060 0 21:53 pts/0 00:00:00 grep --color=auto mysql
总结四步：
mkdir /data/mysql_3307
cd /data/mysql_3307/
mv /var/lib/mysql/* .
chown mysql.mysql /data/mysql_3307
mysql命令行修改参数：
如何查看参数：show global
mysql> show global variables like "port"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 6607 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like "datadir"; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | datadir | /data/mysql_3307/ | +---------------+-------------------+ 1 row in set (0.00 sec) mysql> show global variables like "basedir"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | basedir | /usr/ | +---------------+-------+ 1 row in set (0.00 sec) 修改参数：set global mysql> set global port=3309; ERROR 1238 (HY000): Variable 'port' is a read only variable mysql> set global datadir='/var/lib/mysql'; ERROR 1238 (HY000): Variable 'datadir' is a read only variable
报错九：
ERROR 1238 (HY000): Variable 'port' is a read only variable
这一类参数不能使用set方式修改（动态修改），只能通过修改哦诶之文件重启才能生效。
部分参数可以使用set修改，比如server_id，set修改的参数重启或断开连接会改变初始值，
如果要持久化，比如同时修改my.cnf的配置
mysql> set global server_id=100 -> ; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "server_id"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 100 | +---------------+-------+ 1 row in set (0.00 sec)
global 是对整个server服务生效的，set global后当前连接不生效
session 不加global就是session级的，是对当前会话生效，对整个server不生效，断开连接重新连就失效了
变量分为全局变量和回话级变量
mysql> show global variables like "%char%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> set character_set_client=utf8; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like "%char%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
注意的点：
1.查看server级的参数设置一定要用
show blobal variables like "%char%";
修改server级的变量，一定要用global
set global xxx=xxx
持久化的话一定要改配置文件，避免重启失效
