Home / server / Questions / 87415
Accepted
User1
Asked: 2009-11-24 09:27:24 +0800 CST

Connecting to a new MySQL instance

  • 772

I just created a new MySQL data directory using mysql_install_db:

$mysql_install_db --datadir=/home/user1/opt/mysqld/data/
Installing MySQL system tables...
091123 10:51:54 [Warning] One can only use the --user switch if running as root

OK
Filling help tables...
091123 10:51:54 [Warning] One can only use the --user switch if running as root

OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h machine1 password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com

I ran mysqld_safe to start the instance, but mysql will not connect. I can run mysqld with --skip-grant, but it won't let me set new privileges. How do I kickstart the permissions on a new MySQL instance?

$ps aux|grep mysql

root      2602  0.0  0.0  87076  1308 ?        S    Nov22   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
mysql     2662  0.0  0.2 190676 23732 ?        Sl   Nov22   0:22 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
user1    18954  0.0  0.0  84984  1224 pts/4    S+   11:36   0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=my.cnf
user1    18980  0.0  0.2 190160 22860 pts/4    Sl+  11:36   0:00 /usr/libexec/mysqld --defaults-file=my.cnf --basedir=/usr --datadir=/home/user1/opt/mysqld/data --pid-file=/home/user1/opt/mysqld/mysqld.pid --skip-external-locking --port=3307 --socket=/home/user1/opt/mysqld/mysql.sock
user1    20148  0.0  0.0  82236   756 pts/2    S+   12:15   0:00 grep mysql

netstat -lnp|grep mysql

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      18980/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     153644 18980/mysqld        /home/user1/opt/mysqld/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     8193   -                   /var/lib/mysql/mysql.sock

Edit: There are two instances of MySQL. I want the one on port 3307. mysqld_safe is being run as user1 not root with this command: mysqld_safe --defaults-file=my.cnf

mysql

2 Answers

  1. mysql -P 3307 -u root should do the trick, too.

    • 1
  2. Best Answer

    Ah, it must use a socket file instead of a port.

    mysql --socket=/home/user1/opt/mysqld/mysql.sock -uroot did the trick.

    • 0