Running Multiple MySQL Servers on Linux
The easiest way is to run multiple MySQL servers on Linux is to compile them with different TCP/IP ports and Unix socket files so that each one is listening on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server.
Assume that an existing 5.0.19 server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new 5.5.4 server to have different operating parameters, use a configure command something like this:
# ./configure –with-tcp-port=port_number \
–with-unix-socket-path=file_name \
–prefix=/usr/local/mysql-5.5.4
Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file path name, and the –prefix value should specify an installation directory different from the one under which the existing MySQL installation is located.
Reference: http://dev.mysql.com/
Finding MySQL server operating parameters
If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket file name:
# mysqladmin –host=host_name –port=port_number variables
With the information displayed by that command, you can tell what option values not to use when configuring an additional server.
Exporting and Importing mysql databases
Exporting the Database.
In this example, we will export the ‘mydb’ database into a file called ‘mydb.sql’:
mysqldump -u root -p mydb > mydb.sql
Importing the data is just as easy but involves two steps.
The first step is to create a blank database ready to receive the data.
mysqladmin -u root -p create mydb1
Once done, all that is left is to actually import the data:
mysql -u root -p mydb1 < mydb.sql
MySQL database does not work and returns “Can’t create/write to file” error
The problem is related to the mysql directory permission.
‘mysql’ user is not able to create a temporary file in /var/lib/mysql/ directory due to permissions lack
# ls -lda ~mysql/
drwxr-xr-x 5 root mysql 4096 Aug 31 09:56 /var/lib/mysql/
The solution is to go to /etc/init.d/mysqld_app_init file and change
chown root.mysql /var/lib/mysql
to
chown mysql.mysql /var/lib/mysql
Then restart mysqld service or just change owner of /var/lib/mysql directory to “mysql” instead of “root”
chown root /var/lib/mysql
How to check privileges for an mysql account.
To check the privileges for an account, use SHOW GRANTS:
mysql> SHOW GRANTS FOR ‘root’@'localhost’;
+—————————————————-+
| Grants for root@localhost
+—————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@'localhost’ IDENTIFIED BY PASSWORD ‘*C406D12D7025EFA560629ABD992F09C9E28002C6′ WITH GRANT OPTION |
+—————————————————-+
1 row in set (0.00 sec)
mysql>
