In depth analysis based on the mysql multi instance installation

Believe that most people have encountered too many instance installation mysql, most people believe that as long as 1 to find multiple instance installation tutorial will easily done, but the more smooth installation process more let us uneasy, why, when we are in accordance with the tutorial steps 1 1 came, do you know the meaning of every word of purpose? What should we do after we make a mistake? Now I'm going to show you my installation process, the kinks, the bugs.
First of all, I will explain a scenario: my computer is ubuntu system, and mysql was installed automatically by apt-get. This is the number one reason for the most errors.
Here is my installation process, which is riddled with errors:

6.mkdir mysql
7.groupadd mysql
8.useradd -r -g mysql mysql
# make clean
# rm -f CMakeCache.txt
# rm -rf /etc/my.cnf
9.cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_DATADIR=/usr/local/mysql/data -DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all 
-DENABLED_LOCAL_INFILE=1
make
make install

This means that our installation of mysql source code has been completed. Now we need to initialize the user, which is the most important part of multiple instances. In the future, we should pay attention to the result after the execution of the command at every step:

cd /usr/local/mysql
chown -R root:mysql .
chown -R mysql:mysql data
11.cp support-files/my-medium.cnf /etc/my.cnf
12.cd /usr/local/mysql

<SPAN style="LINE-HEIGHT: 22px; FONT-FAMILY: Verdana,  Song typeface , Helvetica, sans-serif; FONT-SIZE: 14px">scripts/mysql_install_db&nbsp;</SPAN><BR style="LINE-HEIGHT: 22px; FONT-FAMILY: Verdana,  Song typeface , Helvetica, sans-serif; FONT-SIZE: 14px"><SPAN style="LINE-HEIGHT: 22px; FONT-FAMILY: Verdana,  Song typeface , Helvetica, sans-serif; FONT-SIZE: 14px">--defaults-file=/usr/local/mysql/data_3308/my.cnf&nbsp;</SPAN><BR style="LINE-HEIGHT: 22px; FONT-FAMILY: Verdana,  Song typeface , Helvetica, sans-serif; FONT-SIZE: 14px"><SPAN style="LINE-HEIGHT: 22px; FONT-FAMILY: Verdana,  Song typeface , Helvetica, sans-serif; FONT-SIZE: 14px">--datadir=/usr/local/mysql/data_3308/</SPAN>

The mysql_install_db command above is used to initialize the new user. Here's what happens when the command is executed. The first time I executed it, it looked like this:

root@zhou:/usr/local/mysql# scripts/mysql_install_db --defaults-file=/usr/local/mysql/data_3307/my.cnf --datadir=/usr/local/mysql/data_3307/
Installing MySQL system tables...
130107 10:25:47 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'
130107 10:25:47 [ERROR] Aborting
130107 10:25:47 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Here you can see that there has been a mistake, what is the reason, you should be able to see that they are too careless. Caused myself to re-execute cmake once
Then the above command was repeated. Until we see the result: initialization is successful, congratulations you are ready to do the following.

root@zhou:/usr/local/mysql# mysql_install_db --user=mysql --defaults-file=/usr/local/mysql/data3307/my.cnf --datadir=/usr/local/mysql/data3307/
Installing MySQL system tables...
OK
Filling help tables...
OK

At this point, he will generate some basic information such as mysql library and test library in our data directory.
1 must pay attention to the allocation of permissions, 1 accidentally mysql can not read the rights of the relevant files.

<SPAN style="LINE-HEIGHT: 22px; FONT-FAMILY: Verdana,  Song typeface , Helvetica, sans-serif; FONT-SIZE: 14px">mysqld_safe --defaults-file=/usr/local/mysql/data_3307/my.cnf &</SPAN>

130107 13:35:36 [Note] Server socket created on IP: '0.0.0.0'.
130107 13:35:36 [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
130107 13:35:36 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
130107 13:35:36 mysqld_safe mysqld from pid file /usr/local/mysql/data3307/mysql.pid ended

The error here is also obvious because we didn't read host. There are two ways to remedy this. One is to go directly to the directory and attach such a permission to mysql, and the other is to ask the reason why this is so. The reason is that we did not add the parameter --user=mysql when initializing, which resulted in the root permission of the generated file. So that's easy to do and the next time we do it we'll need this command:

root@zhou:/usr/local/mysql# mysql_install_db --user=mysql --defaults-file=/usr/local/mysql/data3307/my.cnf --datadir=/usr/local/mysql/data3307/ --user=mysql

ok, at this time we can check the number of services we have opened through the command 1, and then log in to perform basic operations, permissions, key table, replication, etc.

After starting three mysql servers, I found that I could only log in to the service above 3306, while the rest could not log in. When I killed 3307 3306, there was a mistake in logging in mysql.

root@zhou:/usr/local/mysql/tmp# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

The reason for the error here is that when I did not specify which database server it had a default startup plan, it can be seen here that it was still waiting for the mysql 3306 server.
root@zhou:/etc/init.d# mysql -h127.0.0.1 -P3307