mysql数据库管理-mysql5.6数据库安装并更改存储目录步骤

mysql5.6数据库安装并更改存储目录步骤

（1） 创建目录 

[root@vm01 ~]# mkdir /data/
[root@vm01 ~]# chown mysql:mysql /data
[root@vm01 ~]# chmod 775 data
[root@vm01 ~]# chmod 775 /data
mysql> select @@datadir
    -> ;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

（2） 停止mysql数据库

[root@vm01 mysql]# service mysql stop

（3）拷贝原数据文件到data

[root@vm01 mysql]# cd /var/lib/mysql/
[root@vm01 mysql]# ls -lrt
total 110632
drwxr-xr-x 2 mysql mysql     4096 Oct 22 16:22 test
-rw-rw---- 1 mysql mysql 50331648 Oct 22 16:22 ib_logfile1
drwx------ 2 mysql mysql     4096 Oct 22 16:22 performance_schema
drwx--x--x 2 mysql mysql     4096 Oct 22 16:22 mysql
-rw-r--r-- 1 mysql mysql      111 Oct 22 16:22 RPM_UPGRADE_MARKER-LAST
-rw-r--r-- 1 root  root       111 Oct 22 16:22 RPM_UPGRADE_HISTORY
-rw-rw---- 1 mysql mysql       56 Oct 22 16:25 auto.cnf
drwxrwxr-x 2 mysql mysql     4096 Oct 22 16:55 data
drwx------ 2 mysql mysql     4096 Oct 22 16:57 npmdb
drwx------ 2 mysql mysql     4096 Oct 22 16:57 nmosdb
drwx------ 2 mysql mysql     4096 Oct 22 16:57 kpidb
-rw-rw---- 1 mysql mysql 50331648 Dec 10 15:55 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 Dec 10 15:55 ibdata1
[root@vm01 mysql]# 
[root@vm01 mysql]# cp -rp * /data/
[root@vm01 mysql]# ln -s /data/mysql.sock /var/lib/mysql/mysql.sock
[root@vm01 mysql]#cd /data

[root@vm01 data]# ls -lrt
total 110632
drwxr-xr-x 2 mysql mysql     4096 Oct 22 16:22 test
-rw-rw---- 1 mysql mysql 50331648 Oct 22 16:22 ib_logfile1
drwx------ 2 mysql mysql     4096 Oct 22 16:22 performance_schema
drwx--x--x 2 mysql mysql     4096 Oct 22 16:22 mysql
-rw-r--r-- 1 mysql mysql      111 Oct 22 16:22 RPM_UPGRADE_MARKER-LAST
-rw-r--r-- 1 root  root       111 Oct 22 16:22 RPM_UPGRADE_HISTORY
-rw-rw---- 1 mysql mysql       56 Oct 22 16:25 auto.cnf
drwxrwxr-x 2 mysql mysql     4096 Oct 22 16:55 data
drwx------ 2 mysql mysql     4096 Oct 22 16:57 npmdb
drwx------ 2 mysql mysql     4096 Oct 22 16:57 nmosdb
drwx------ 2 mysql mysql     4096 Oct 22 16:57 kpidb
-rw-rw---- 1 mysql mysql 50331648 Dec 10 15:55 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 Dec 10 15:55 ibdata1

（4）编辑my.cnf文件

[root@vm01 data]# 
[mysqld]
#socket=/var/lib/mysql/mysql.sock
socket=/data/mysql.sock
datadir=/data
user=mysql
skip-grant-tables
# 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

（5）启动mysql

[root@vm01 data]# service mysql start
Starting MySQL.... SUCCESS! 

-bash-4.1$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

-bash-4.1$ mysql -S /data/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| kpidb              |
| mysql              |
| nmosdb             |
| npmdb              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> select @@datadir
    -> ;
+-----------+
| @@datadir |
+-----------+
| /data/    |
+-----------+
1 row in set (0.00 sec)

mysql> 

mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name                           | Value                      |
+-----------------------------------------+----------------------------+
| basedir                                 | /usr                       |
| binlog_direct_non_transactional_updates | OFF                        |
| character_sets_dir                      | /usr/share/mysql/charsets/ |
| datadir                                 | /data/                     |
| ignore_db_dirs                          |                            |
| innodb_data_home_dir                    |                            |
| innodb_log_group_home_dir               | ./                         |
| innodb_max_dirty_pages_pct              | 75                         |
| innodb_max_dirty_pages_pct_lwm          | 0                          |
| innodb_undo_directory                   | .                          |
| lc_messages_dir                         | /usr/share/mysql/          |
| plugin_dir                              | /usr/lib64/mysql/plugin/   |
| slave_load_tmpdir                       | /tmp                       |
| tmpdir                                  | /tmp                       |
+-----------------------------------------+----------------------------+
14 rows in set (0.00 sec)

-bash-4.1$ more vm01.pid 
4408
-bash-4.1$ ps -ef|grep 4408
mysql      4408   4218  0 16:00 pts/1    00:00:03 /usr/sbin/mysqld --basedir=/usr --datadir=/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/data/vm01.pid --socket=/data/mysql.sock
mysql      4588   4445  0 16:06 pts/1    00:00:00 grep 4408
-bash-4.1$ 

一、环境安装

首先需要rpm包以及基础环境 
mysql-community-release-el6-5.noarch.rpm 
博主分享rpm包 
yum -y install gcc automake autoconf libtool make gcc-c++ pcre-devel zlib-devel openssl-devel wget vim

二、安装mysql

安装rpm包

rpm -ivh mysql-community-release-el6-5.noarch.rpm

yum安装mysql

yum install mysql-community-server -y

mysql安装完成

三、配置mysql

首先启动mysql

service mysqld start

设置密码及安全规则

mysql_secure_installation

这一步骤我只修改了密码，其它安全规则根据需求修改

修改配置文件（这里我修改的为/home目录下）

datadir=/home/mysql 
socket=/home/mysql/mysql.sock

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
character_set_server=utf8
max_allowed_packet = 512M
innodb_log_file_size = 512M
federated
event_scheduler = 1

#
# 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=/home/mysql
socket=/home/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

将原目录下的所有文件copy到 /home下面

cp -rp /var/lib/mysql /home/

这里注意需要拷贝所有的文件包括相对应的权限

在做一下软链接，否则会出现启动不了的情况

ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock

最后冲洗一下mysql

service mysqld restart

四、注意情况

在这里可能会出现无法启动的情况，还要配置一下selinux，然后重启机器

vi /etc/selinux/config 
将selinux配置为disabled，当然，有其它需求的可以自己配置规则

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

重启服务后再启动mysql就可以啦~