MySQL初步安裝後更改datadir目錄幾種方式

shy丶gril發表於2016-05-24
MySql

標題：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
  …. 解除安裝完成..
  
  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正… 


相關文章