MySQL changes the default data document storage directory

Source: Internet
Author: User
Tags chmod unique id

The MySQL default data document storage directory is/var/lib/mysql. The following steps are required if you want to move the MySQL directory to/home/data:


1. Create a Directory

Cd/opt && mkdir Data

2. Stop the MySQL service process

Mysqladmin-u root-p shutdown. or service mysqld stop

3. Move/var/lib/mysql Entire directory to/home/data
mv/var/lib/mysql/*/opt/data/
This moves the MySQL data document to the/home/data/.

4. Locate the MY.CNF configuration document

If there is no MY.CNF configuration document under the/etc/directory, locate the *.CNF document under/usr/share/mysql/, and copy one of them to/etc/and rename it to MY.CNF).
The command is as follows:
[Email protected] mysql]# CP/USR/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF

5. Edit the configuration document for MySQL/ETC/MY.CNF
To ensure that MySQL works correctly, you need to indicate where the Mysql.sock document is generated. Modify the value in the Socket=/var/lib/mysql/mysql.sock line to the right of the equals sign:/home/mysql/mysql.sock. The operation is as follows:.
VI My.cnf (use VI tool to edit the MY.CNF document, find the following data modification, some versions are some, you don't have to change).
# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" Comment this line)
Socket =/opt/data/mysql/mysql.sock (plus this line).

6. Modify MySQL startup script/etc/init.d/mysql
Finally, you need to modify the MySQL startup script/etc/init.d/mysql, the path to the right of the equal sign in the Datadir=/var/lib/mysql line is changed to your current actual storage path: Home/data/mysql.
[Email protected] etc]# Vi/etc/init.d/mysql
#datadir =/var/lib/mysql (Note this line).
Datadir=/opt/data/mysql (plus this line):

7. Restart MySQL Service


/etc/init.d/mysql start ... or service mysqld start

8. Configuration File Example

[[email protected] etc]# more/etc/my.cnf
# Example MySQL config file for medium systems.
#
# This was for a system with little memory (32m-64m) where MySQL plays
# A important part, or systems up t o 128M where MySQL is used together with
# Other programs (such as a Web server)
#
# mysql programs look for O ption files in a set of
# locations which depend on the deployment platform.
# can copy this option for file to one of those
# locations. For information on these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# in this File, you can use the all long options, the a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options would be passed to all MySQL clients
[Client]
#password = Your_password
Port = 3306
#socket =/var/lib/mysql/mysql.sock
Socket =/opt/data/mysql.sock

# here follows entries for some specific programs

# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock
Socket =/opt/data/mysql.sock
Skip-external-locking
Key_buffer_size = 16M
Max_allowed_packet = 1M
Table_open_cache = 64
Sort_buffer_size = 512K
Net_buffer_length = 8K
Read_buffer_size = 256K
Read_rnd_buffer_size = 512K
Myisam_sort_buffer_size = 8M

# Don ' t listen on a TCP/IP port at all. This can is a security enhancement,
# If all processes this need to connect to mysqld run on the same host.
# All interaction with Mysqld must is made via Unix sockets or named pipes.
# Note that the using this option without enabling named Pipes on Windows
# (via the "enable-named-pipe" option) would render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# Binary logging is required for replication
Log-bin=mysql-bin

# Binary Logging format-mixed recommended
Binlog_format=mixed

# required Unique ID between 1 and 2^32-1
# defaults to 1 if master-host are not set
# but would not function as a master if omitted
Server-id = 1

# Replication Slave (Comment out master sections to use this)
#
# To configure the host as a replication slave, you can choose between
# methods:
#
# 1) Use the Change MASTER to command (fully described in our manual)-
# The syntax is:
#
# change MASTER to Master_host=# master_user=<user>, master_password=<password>;
#
# where you replace the # <port> by the master's port number (3306 by default).
#
# Example:
#
# change MASTER to master_host= ' 125.564.12.1 ', master_port=3306,
# master_user= ' Joe ', master_password= ' secret ';
#
# OR
#
# 2) Set the variables below. However, in case you choose the This method, then
# Start replication for the first time (even unsuccessfully, for example
# If you mistyped the password in Master-password and the slave fails to
# Connect), the slave would create a master.info file, and any later
# change in this file to the variables ' values below'll be ignored and
# Overridden by the content of the Master.info file, unless you shutdown
# The slave server, delete master.info and restart the slaver server.
# for this reason, want to leave the lines below untouched
# (commented) and instead use change MASTER to (see above)
#
# required Unique ID between 2 and 2^32-1
# (and different from the master)
# defaults to 2 if Master-host is set
# but would not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave-required
#master-host = #
# The username the slave would use for authentication when connecting
# to the master-required
#master-user = <username>
#
# The password the slave would authenticate with when connecting to
# The Master-required
#master-password = <password>
#
# The Port the master is listening on.
# Optional-defaults to 3306
#master-port = <port>
#
# binary Logging-not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you is using InnoDB tables
#innodb_data_home_dir =/var/lib/mysql
#innodb_data_file_path = Ibdata1:10m:autoextend
#innodb_log_group_home_dir =/var/lib/mysql
# you can set: _buffer_pool_size up to 50-80%
# of RAM But beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set: _log_file_size to% of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash
# Remove The next comment character if you is not a familiar with SQL
#safe-updates

[Myisamchk]
Key_buffer_size = 20M
Sort_buffer_size = 20M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout

[Email protected] etc]#
[Email protected] etc]#
[Email protected] etc]#
[Email protected] etc]# More/etc/init.d/mysqld
#!/bin/sh
#
# mysqld This shell script takes care of starting and stopping
# The MySQL subsystem (MYSQLD).
#
# Chkconfig:-64 36
# Description:mysql database server.
# Processname:mysqld
# config:/etc/my.cnf
# Pidfile:/var/run/mysqld/mysqld.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source Networking configuration.
. /etc/sysconfig/network


Exec= "/usr/bin/mysqld_safe"
Prog= "Mysqld"

# Set Timeouts They can be overridden from/etc/sysconfig/mysqld
starttimeout=120
Stoptimeout=60

[-e/etc/sysconfig/$prog] &&. /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog


# extract value of a MySQL option from config files
# usage:get_mysql_option Section VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; Hence take the last match.
Get_mysql_option () {
result= '/usr/bin/my_print_defaults ' | Sed-n "s/^--$2=//p" | Tail-n 1 '
If [-Z "$result"]; Then
# found, use default
result= "$"
Fi
}

#get_mysql_option mysqld datadir "/var/lib/mysql"
Get_mysql_option mysqld datadir "/opt/data/mysql"
Datadir= "$result"
Get_mysql_option mysqld Socket "$datadir/mysql.sock"
Socketfile= "$result"
Get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
Errlogfile= "$result"
Get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
Mypidfile= "$result"


Start () {
[-X $exec] | | Exit 5
# Check to see if it ' s already running
Response= '/usr/bin/mysqladmin--socket= "$socketfile"--user=unknown_mysql_user ping 2&gt;&1 '
If [$ = 0]; Then
# already running, do nothing
Action $ "Starting $prog:"/bin/true
Ret=0
elif echo "$RESPONSE" | Grep-q "Access denied for user"
Then
# already running, do nothing
Action $ "Starting $prog:"/bin/true
Ret=0
Else
# Prepare for Start
Touch "$errlogfile"
Chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[-x/sbin/restorecon] &&/sbin/restorecon "$errlogfile"
if [!-d "$datadir/mysql"]; Then
# First, make sure $datadir are there with correct permissions
if [!-e "$datadir"-A!-h "$datadir"]
Then
Mkdir-p "$datadir" | | Exit 1
Fi
Chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
[-x/sbin/restorecon] &&/sbin/restorecon "$datadir"
# now create the database
Action $ "Initializing MySQL database:"/usr/bin/mysql_install_db--datadir= "$datadir"--user=mysql
Ret=$?
Chown-r mysql:mysql "$datadir"
If [$ret-ne 0]; Then
Return $ret
Fi
Fi
Chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# Pass All the options determined above, to ensure consistent behavior.
# In many cases Mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure. (An exception are that we don ' t force the
# Log-error setting, since this script doesn ' t really depend on that,
# and some users might prefer to configure logging to syslog.)
# Note:set--basedir To prevent probes that might trigger SELinux
# alarms, per bug #547485
$exec--datadir= "$datadir"--socket= "$socketfile" \
--pid-file= "$mypidfile" \
--BASEDIR=/USR--user=mysql &gt;/dev/null 2&gt;&1 &
safe_pid=$!
# Spin for a maximum of N seconds waiting for the server to come up;
# exit the loop immediately if Mysqld_safe process disappears.
# rather than assuming we know a valid username, accept an "access
# denied "response as meaning the server is functioning.
Ret=0
timeout= "$STARTTIMEOUT"
While [$TIMEOUT-GT 0]; Do
Response= '/usr/bin/mysqladmin--socket= "$socketfile"--user=unknown_mysql_user ping 2&gt;&1 ' && Break
echo "$RESPONSE" | Grep-q "Access denied for user" && break
if! /bin/kill-0 $safe _pid 2&gt;/dev/null; Then
echo "MySQL Daemon failed to start."
Ret=1
Break
Fi
Sleep 1
Let Timeout=${timeout}-1
Done
If [$TIMEOUT-eq 0]; Then
echo "Timeout error occurred trying to start MySQL Daemon."
Ret=1
Fi
If [$ret-eq 0]; Then
Action $ "Starting $prog:"/bin/true
Touch $lockfile
Else
Action $ "Starting $prog:"/bin/false
Fi
Fi
Return $ret
}

Stop () {
if [!-F "$mypidfile"]; Then
# not running; Per LSB standards this is "OK"
Action $ "Stopping $prog:"/bin/true
return 0
Fi
Mysqlpid= ' Cat ' $mypidfile '
If [-N "$MYSQLPID"]; Then
/bin/kill "$MYSQLPID" &gt;/dev/null 2&gt;&1
Ret=$?
If [$ret-eq 0]; Then
timeout= "$STOPTIMEOUT"
While [$TIMEOUT-GT 0]; Do
/bin/kill-0 "$MYSQLPID" &gt;/dev/null 2&gt;&1 | | Break
Sleep 1
Let Timeout=${timeout}-1
Done
If [$TIMEOUT-eq 0]; Then
echo "Timeout error occurred trying to stop MySQL Daemon."
Ret=1
Action $ "Stopping $prog:"/bin/false
Else
Rm-f $lockfile
Rm-f "$socketfile"
Action $ "Stopping $prog:"/bin/true
Fi
Else
Action $ "Stopping $prog:"/bin/false
Fi
Else
# failed to read Pidfile, probably insufficient permissions
Action $ "Stopping $prog:"/bin/false
Ret=4
Fi
Return $ret
}

Restart () {
Stop
Start
}

Condrestart () {
[-e $lockfile] && Restart | | :
}


# See how we were called.
Case "$" in
Start
Start
;;
Stop
Stop
;;
Status
Status-p "$mypidfile" $prog
;;
Restart
Restart
;;
Condrestart|try-restart)
Condrestart
;;
Reload
Exit 3
;;
Force-reload)
Restart
;;
*)
echo $ "Usage: $ {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
Exit 2
Esac

Exit $?

[Email protected] etc]#
[Email protected] etc]# chkconfig--add mysqld
[Email protected] etc]# chkconfig--level mysqld on
[Email protected] etc]#
[Email protected] etc]#

MySQL changes the default data document storage directory

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.