introduction
MySQL is a relational
database management system developed by the Swedish MySQL AB company and currently belongs to Oracle's products. MySQL is one of the most popular relational database management systems. In terms of web applications, MySQL is the best RDBMS (Relational Database Management System) application software.
MySQL is a relational database management system. Relational databases store data in different tables instead of putting all data in one large warehouse, which increases speed and flexibility.
MySQL
Questions about how to change the datadir directory:
When ubuntu installs mysql by default, it will set datadir to
/var/lib/mysql
Below, but most of the time we need to specify a directory that we have prepared for easy searching as the data storage directory, we can
my.cnf
Change below
datadir
This line will be'
=
'The following directory can be changed to our own directory.
For example: I changed datadir to
/data
, Then make the following changes in my.cnf, in
[mysqld]
segment
port = 3306
basedir = /usr
datadir = /data/mysql
After the changes are completed, save and exit, you can restart the mysql service. I don't know if your server will report an error. My mysql can't get up anyway. Just change the datadir back to start. The reason for this problem is that there is an apparmor service in ubuntu.
The main function of this service is to set the access control authority of an executable program, which can restrict the program to read/write a certain directory/file, open/read/write network ports, and so on. (It turned out that although we said that the owner of the new directory was changed to mysql, we did not tell mysql what permissions to give to the new directory, so it was a tragedy)
His profile is in
/etc/apparmor.d/
In, here we can see a
usr.sbin.mysqld
The configuration file, open it and see it. Our log path, pid path, etc. are all stored here, so we need to change the datadir path, which also needs to be changed here. If you want to change the log path, you must also change it here. The configuration after the change is as follows:
/logs/mysql/mysql.log rw,
/logs/mysql/mysql.err.log rw,
/data/mysql/ r,
/data/mysql/** rwk,
/logs/mysql/ r,
/logs/mysql/* rw,
This is the path I changed. After the change is completed, because this is a service, we need to restart this service.
/etc/init.d/apparmor restart
There is basically no problem here, (I just restarted the server here and it was OK), if you still can’t start your mysql, then please use the following command
mysql_install_db --datadir=/data/mysql
Check if there is an error message, and check according to the error.
Finally the problem was solved according to this method. Thank you, senior.
Finally, I make a summary, just as an attempted solution:
Change the mysql default datadir directory "/var/lib/mysql" to "/home/mysql_data"
1. Turn off the database
sudo /etc/init.d/mysql stop
2. Because the database file directory we specified is /home/mysql_data
cd /home //Open home
mkdir mysql_data //Create a directory
chown mysql:mysql mysql_data //and modify its owner and group to mysql:mysql.command
//Modify the mysql configuration file my.cnf:
Change datadir=/var/lib/mysql to datadir=/home/mysql_data
3. Modify the security settings in ubuntu
sudo gedit /etc/apparmor.d/usr.sbin.mysqld
Add permission settings in this file to change the original
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
Replace with (or add directly)
/home/mysql_data/mysql/ r,
/home/mysql_data/mysql/** rwk,
4. Reinitialize the data file: execute
sudo mysql_install_data
5. Start the mysql database service (or restart the server):
sudo /etc/init.d/mysql start
The most worth mentioning of this method is the modification
/etc/apparmor.d/usr.sbin.mysqld
This file changes the permissions of the application.
MySQL index implementation
In MySQL, the index belongs to the concept of the storage engine level. Different storage engines implement indexes in different ways. This article mainly discusses the index implementation methods of the MyISAM and InnoDB storage engines.
MyISAM index implementation
The MyISAM engine uses B+Tree as the index structure, and the data field of the leaf node stores the address of the data record.
There are three columns in the table here, suppose we use Col1 as the primary key. It can be seen that the MyISAM index file only saves the address of the data record. In MyISAM, there is no difference in structure between the primary index and the secondary index (Secondary key), but the primary index requires the key to be unique, and the key of the secondary index can be repeated.
It is also a B+Tree, and the data field saves the address of the data record. Therefore, the index retrieval algorithm in MyISAM is to first search the index according to the B+Tree search algorithm. If the specified Key exists, then take out the value of its data field, and then use the value of the data field as the address to read the corresponding data record.
MyISAM's indexing method is also called "non-clustered", and the reason for this is to distinguish it from InnoDB's clustered index.
