MYSQL multi-instance operation

MYSQL multi-instance running mysql can implement a server in the form of multiple instances and run mysql with different data files on different ports. They are independent of each other. 1. Disable mysql on the original default port 3306: servicemysqdstop2. Copy or create a data file? # Copying existing mysql database files # copy files in varlibmysql

MYSQL multi-instance running mysql can implement a server in the form of multiple instances and run mysql with different data files on different ports. They are independent of each other. 1. Disable mysql: service mysqd stop on the original default port 3306. 2. Copy or create a data file? # Copy an existing mysql database file # copy files in/var/lib/mysql

MYSQL multi-instance running

Mysql can implement a server in the form of multiple instances and run mysql with different data files on different ports. They are independent of each other.

1. Disable mysql: service mysqd stop on the default port 3306.

2. Copy or create a data file

?

# Copy an existing mysql database file # copy one to the mysql_3307 folder in/var/lib/mysql. [root @ test-206 ~] # Cp-r/var/lib/mysql/var/lib/mysql_3307

?

# Create a new empty database [root @ test-206 ~] # Mkdir/var/lib/mysql_3307 [root @ test-206 ~] # Mysql_install_db -- datadir =/var/lib/mysql_3307 -- user = mysql

? 3. Assign mysql users and user groups to data files

[root@test-206 ~]# chown -R mysql.mysql /var/lib/mysql_3307

? 4. Create a multi configuration cnf file to start the mysql instance (for example, 3307) and load and execute

[root@test-206 ~]# touch /usr/local/my_multi.cnf

? Write the configuration you want in the file. The following is a typical configuration:

[Mysqld_multi] mysqld =/usr/bin/mysqld_safemysqladmin =/usr/bin/mysqladminuser = root # password used to log on to and disable this service = 123456 # Same as [mysqld3307] socket =/tmp/ mysql_3307.sockport = 3307pid-file =/var/lib/mysql_3307/3307. piddatadir =/var/lib/mysql_3307/log =/var/lib/mysql_3307/3307. logcharacter-set-server = utf8user = mysql

? 5. Start your multi-instance

[root@test-206 ~]# mysqld_multi --defaults-extra-file=/usr/local/my_multi.cnf start 3307

? 6. Check whether the startup is successful.

[root@test-206 ~]# netstat -ntlptcp        0      0 :::3306                     :::*                       LISTEN      3919/mysqldtcp        0      0 :::3307                     :::*                        LISTEN      15027/mysqld

?

If no mysql instance is found, check the/var/lib/mysql_3307/3307. log File to troubleshoot the problem.

7. Set a new password

[root@test-206 ~]#  mysqladmin -uroot -S /tmp/mysql_3307.sock password 123456

? 8. log on to your new instance

[root@test-206 ~]# mysql -uroot -S /tmp/mysql_3307.sock -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.20-log Distributed by The IUS Community ProjectCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

? Check again whether the data in the mysql_3307 folder is used for your instance.

mysql> show variables like '%datadir%';+---------------+----------------------+| Variable_name | Value                |+---------------+----------------------+| datadir       | /var/lib/mysql_3307/ |+---------------+----------------------+row in set (0.00 sec)mysql>

? Well, no error! Finally, engage in permissions, users, and so on. Close!

# Query user mysql> select user, host from mysql. user; + ------ + ----------- + | user | host | + ------ + ----------- + | root | 127.0.0.1 | root | :: 1 | localhost | root | localhost | test-206 | root | test-206 | + ------ + ----------- + rows in set (0.00 sec) # Set the permission for mysql> grant all on *. * to root @ '%' identified by 'root' with grant option; Query OK, 0 rows affected (0.00 sec) # Query permission mysql> show grants for root; # create mysql> grant select on *. * to backup @ '%' identified by 'backup '; Query OK, 0 rows affected (0.00 sec)

?