How to modify MySQL datadir in Ubuntu10.04 _ MySQL

Ubuntu for modifying MySQL datadir under Ubuntu10.04

BitsCN.com

Because the server space is tight, you need to move mysql datadir under Ubuntu 10.04 from the default/var/lib/mysql to/usr/local/mysql. The operation is as follows:
1. changed/etc/mysql/my. cnf to datadir =/usr/local/mysql.
2. cp-a/var/lib/mysql/usr/local/
3./etc/init. d/mysql start

If the problem occurs, the system reports an error and cannot start mysql. The log shows: Can't find file: "./mysql/plugin. frm' (errno: 13)
[ERROR] Can't open the mysql. plugin table. Please run mysql_upgrade to create it.
However, an error is returned when mysql_upgrade is executed.
No solution was found after google circle on the Internet.
I suddenly remembered that when I was debugging the mail server, I had to modify the system's default storage directory and the system's chroot parameter. so I decided to try it out. it turns out this is the reason. Here we will record the relevant steps for sharing with friends who need them.
To modify the chroot of the system, you need to modify the related files under/etc/apparmor. d. here, we use mysql as an example. you need to modify the two files: usr. sbin. mysqld and abstractions/mysql.
1. modify usr. sbin. the following two lines in mysqld:/var/lib/mysql/r. change to/usr/local/mysql/r,/var/lib/mysql/** rwk, and change: /usr/local/mysql/** rwk,
2. modify the statement actions/mysql line:/var/lib/mysql. sock rw to/usr/local/mysql. sock rw,
3. reload the apparmor service:/etc/init. d/apparmor reload
Start mysql again!
Revelation: It's not just mysql. if you need to modify the storage directory of some other built-in software, but still report an error after the configuration is changed, you may need to modify the corresponding chroot! Please refer to the above method!
The key points are as follows:

1. find the Option File loaded during mysql startup (if there are multiple Option files, it must be the one that works ). There is only one on my machine, which is "/etc/mysql/my. cnf". go in and change the value of datadir to the specified directory. This is not difficult.

2. the most important thing is to transfer everything in the original data directory (default: "/var/lib/mysql") to the new directory! Yes! Make sure that the related content remains in the new directory with the same user and permission settings as before. With chown and chmod, it's easy. However, if this step goes wrong, it will be difficult to find it. when you restart mysql, it will only say that it cannot be connected, and it is difficult to find the real cause. I installed MySQL through Package Manager in Ubuntu. after installation, the user name and group name in the relevant files and directories are "mysql", so the new datadir should also set the owner to mysql, and make sure that it has the appropriate permissions.

3. ensure that the datadir user/permission settings are correct. if the datadir directory is deep, it seems to be related to the permissions of the above directories. In short, ensure that the user "mysql" can smoothly access the new datadir content, and ensure that all necessary content has been copied to the new datadir. In MySQL, the information required for starting such records is also saved in the form of a database. mysql loads these required databases at startup. if the permission on the related directory is incorrect, or the file does not exist.

4. about AppArmor, the online documents said that the changes need to be told through the profile File. otherwise, AppArmor will prevent mysql from accessing the new file path and thus mysql will not run. In my experience, I don't seem to feel the influence of AppArmor. For the sake of security, modify its profile file according to the internet. on my machine, it is: "/etc/apparmor. d/usr. sbin. mysqld ", add a few lines according to the format of the original datadir content:

/My/new/datadir/r,
/My/new/datadir/** rwk,

5. both AppArmor and mysql must be restarted. The restart command is:

Sudo/etc/init. d/{apparmor | mysql} stop
Sudo/etc/init. d/{apparmor | mysql} start

You can also use the service command:

Sudo service {apparmor | mysql} stop
Sudo service {apparmor | mysql} start

---- TroubleShooting:

The mysql Error log file is "/var/log/mysql/error. log" on my machine. if a problem occurs, the real cause is recorded here. Don't worry too much about the output of several lines in the terminal. basically, no matter what happens, you can only say that you cannot connect to the Server, which is useless.

Author "bored pp"

BitsCN.com