mysql新建data的方法_MySQL 初步安装后更改datadir目录几种方式

作者：

ｌōττéｒｙ

©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

注释：

若mysql 安装初步使用rpm默认将db安装到/var/lib/mysql/路径后，想更改db路径，怎么处理呢？

测试步骤：

1、 简单叙述安装步骤

2、更改db目录的2中方式..

①. 通过db初始化命令更改db存放位置   /usr/bin/mysql_install_db–user=mysql –basedir=/usr/ –datadir=/data/public3306/db/

②. 通过将原来db目录内内容拷贝到新地址，再更改my.cnf 文件的datadir (类似oracle 冷备，更改控制文件 datafile指针..)

3、卸载搭建的测试环境

一、简单叙述安装步骤

–> 准备安装包

[root@lottery ~]# ll

-rw-rw-rw-  1 root root  18360416 5月  19 10:54 MySQL-client-5.6.19-1.el6.x86_64.rpm

-rw-rw-rw-  1 root root  54592892 5月  19 10:54 MySQL-server-5.6.19-1.el6.x86_64.rpm

–>rpm -vhi 安装包

[root@lottery ~]# rpm -hvi MySQL-client-5.6.19-1.el6.x86_64.rpm

Preparing…                ########################################### [100%]

1:MySQL-client      ########################################### [100%]

[root@lottery ~]# rpm -hvi MySQL-server-5.6.19-1.el6.x86_64.rpm

Preparing…                ########################################### [100%]

1:MySQL-server     ########################################### [100%]

–>启动mysql服务

[root@lottery ~]#  /etc/init.d/mysql start

Starting MySQL… SUCCESS!

–>初始化密码

# mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

# mysql -uroot

mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’;

mysql> FLUSH PRIVILEGES;

mysql> quit

# /etc/init.d/mysqld restart

# mysql -uroot -p

Enter password:

mysql>   show variables like ‘%datadir%’;

+—————+———————–+

| Variable_name | Value                           |

+—————+———————–+

| datadir             | /var/lib/mysql/           |

+—————+———————–+

mysql>

二、更改db目录的2中方式..

①. 通过db初始化命令更改db存放位置   /usr/bin/

mysql_install_db

–user=mysql –basedir=/usr/ –datadir=/data/public3306/db/

—  将之前数据库停掉–用mysql_install_db 脚本 初始化(新建库)到/data/public3306/db/ 目录的数据库..

—  若原数据库有新建的database ，需要将database对应的目录cp -rp 到更改后的路径/data/public3306/db/ ；

—  否则原新的database不会在新路径体现….

[root@lottery public3306]# /usr/bin/mysql_install_db –user=mysql –basedir=/usr/ –datadir=/data/public3306/db/

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 lottery 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 mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr//my.cnf on the system.

Because this file might be in use, it was not replaced,

but was used in bootstrap (unless you used –defaults-file)

and when you later start the server.

The new default config file was created as /usr//my-new.cnf,

please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

–defaults-file argument to mysqld_safe when starting the server

[root@lottery public3306]#  /etc/init.d/mysql start

Starting MySQL….

SUCCESS!

[root@lottery public3306]# cd db

[root@lottery db]# ll

总用量 2097172

-rw-rw—- 1 mysql mysql         56 5月  19 15:47 auto.cnf

drwx—— 2 mysql mysql       4096 5月  19 15:46 mysql

drwx—— 2 mysql mysql       4096 5月  19 15:46 performance_schema

drwx—— 2 mysql mysql       4096 5月  19 15:46 test

[root@lottery db]#

②. 通过将原来db目录内内容拷贝到新地址，再更改my.cnf 文件的datadir

—  测试之前将原database，执行create database lottery ，利于迁移后是否正确.

–> 原路径

[root@lottery mysql]# pwd

/var/lib/mysql/

–> 目录开头的为database

[root@lottery mysql]# ll

-rw-rw—- 1 mysql mysql         56 5月  19 15:47 auto.cnf

drwx—— 2 mysql mysql       4096 5月  19 15:52

lottery

drwx—— 2 mysql mysql       4096 5月  19 15:46 mysql

drwx—— 2 mysql mysql       4096 5月  19 15:46 performance_schema

drwx—— 2 mysql mysql       4096 5月  19 15:46 test

–> 查看lottery 数据库的表

[root@lottery lottery]# ll  lottery/*

-rw-rw—- 1 mysql mysql    54 5月  19 15:47 db.opt

-rw-rw—- 1 mysql mysql  8560 5月  19 15:52 t1.frm

—t1表 一张

-rw-rw—- 1 mysql mysql 98304 5月  19 15:52 t1.ibd

–> 创建新datadir目录

[root@lottery lottery ]# mkdir -p /data/public3306/db

—  参数-p： 级联创建

–> 整个mysql datadir进行拷贝

[root@lottery lottery ]# cp -pr /var/lib/mysql/ /data/public3306/db

—  参数-p：连带属性拷贝，-r是目录拷贝

–> 检查cp 的文件内容

[root@lottery lottery  ]# cd /data/public3306/db

[root@lottery db]# ll

-rw-rw—- 1 mysql mysql         56 5月  19 15:47 auto.cnf

drwx—— 2 mysql mysql       4096 5月  19 15:52

lottery

drwx—— 2 mysql mysql       4096 5月  19 15:46 mysql

drwx—— 2 mysql mysql       4096 5月  19 15:46 performance_schema

drwx—— 2 mysql mysql       4096 5月  19 15:46 test

–> 更改my.cnf 的datadir参数，将原来的/var/lib/mysql 改成 /data/public3306/db

[root@lottery db]# vi /etc/my.cnf

–> 启动mysql服务

[root@lottery db]# /etc/init.d/mysql start

Starting MySQL…..

SUCCESS

!

–> 查看更改后datadir是否有效的2种方式

–> OS层查看

[root@lottery db]# ps -ef | grep mysql

root     11346     1  0 16:07 pts/6    00:00:00 /bin/sh /usr/bin/mysqld_safe

–datadir=/data/public3306/db

–pid-file=/data/public3306/mysqld.pid

mysql    12007 11346 17 16:07 pts/6    00:00:03 /usr/sbin/mysqld –basedir=/usr

–datadir=/data/public3306/db

–plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/data/public3306/err.log –pid-file=/data/public3306/mysqld.pid –socket=/data/public3306/mysql.sock –port=3306

–> DB层查看

mysql> show variables like ‘%datadir%’;

+—————+———————–+

| Variable_name | Value                 |

+—————+———————–+

| datadir            |

/data/public3306/db/ |

+—————+———————–+

mysql>

–> 检查下原来database lottery 中的t1表 再新的环境中是否存在

mysql> use lottery;

Database changed

mysql> show tables;

+——————-+

| Tables_in_lottery |

+——————-+

|

t1

|

+——————-+

mysql>

三、卸载搭建的测试环境

–> 查看系统中是否以rpm包安装的mysql

[root@lottery db]# rpm -qa | grep -i mysql

MySQL-client-5.6.19-1.el6.x86_64

MySQL-server-5.6.19-1.el6.x86_64

–> 通过rpm -e 卸载软件包

[root@lottery db]# rpm -e MySQL-client-5.6.19-1.el6.x86_64

[root@lottery db]# rpm -e MySQL-server-5.6.19-1.el6.x86_64

–> 查看是否有mysql服务，删除 mysql服务命令：chkconfig –del mysql

[root@lottery db]# chkconfig –list | grep -i mysql

–> mysql程序命令进行删除

[root@lottery db]# whereis mysql

mysql: /usr/lib64/mysql

[root@lottery db]# rm  -rf /usr/lib64/mysql

…. 卸载完成..

【源于本人笔记】 若有书写错误，表达错误，请指正…