Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can connect with localhost:3307, but there aren't any tables/databases. I'm fairly sure /etc/my.cnf is right:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = ***

[mysqld]
port       = 3306
socket   = /var/run/mysql/mysql.sock

[mysqld2]
port       = 3307
socket   = /var/run/mysql/mysql2.sock

What can I do now? How can I sanely restart the MySQL server?

Perhaps more importantly:

How can I make sure all the tables still exist?

share|improve this question

migrated from stackoverflow.com May 31 '11 at 15:47

This question came from our site for professional and enthusiast programmers.

    
Where can you connect with localhost:3307? In a php app? –  jcolebrand May 31 '11 at 15:50

2 Answers 2

Here is one thing I quickly see is missing : Each [mysqld] needs to have its own datadir. No two mysqld groups can share the same datadir. You need to allocate different datadirs in different folders and make sure you run "chown -R mysql:mysql (datadir folder)" on all datadirs.

Your /etc/my.cnf should look something like this:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = ***

[mysqld]
port       = 3306
datadir  = /var/lib/mysql1
socket   = /var/run/mysql/mysql.sock

[mysqld2]
port       = 3307
datadir  = /var/lib/mysql2
socket   = /var/run/mysql/mysql2.sock

Check out this link from the Pythian Group on how to start and stop the individual DB services.

From my answer, you should see that there must be a complete copy of the data in /var/lib/mysql1, /var/lib/mysql2, etc.

share|improve this answer

This is how I run multis:

  1. Make a "my.cnf" for each instance of mysql specifying your port and socket. e.g. Port 3307

    [mysqld]

    port = 3307

    socket = /opt/db/3307/mysql.sock

    datadir = /opt/db/3307

    tmpdir = /opt/tmp/3307

    ...

  2. Start each instance using its own include file.

e.g.

/usr/bin/mysqld_safe --defaults-file=/etc/my_3306.cnf
/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf
/usr/bin/mysqld_safe --defaults-file=/etc/my_3308.cnf

This way I can control each individual instance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.