Wow, I found the problem! I think this may be a bug. In my case, I've got three instances running on ports 3306, 3307, and 3320. On the local machine, I connect to them via
mysql -p --port-3306 --host=localhost mysql -p --port-3307 --host=localhost mysql -p --port-3320 --host=localhost Except that connecting to port 3307 doesn't really happen. It seems that the command line client connects to the main instance via the default socket when the host is not specified, or is the value localhost. So, even though I've specified the host and port, I wind up connecting to the main instance. I found this out by stopping the main instance on 3306, and then I issue: LX09:/etc # mysql --port=3307 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=localhost ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. LX09:/etc # mysql --port=3307 --host=nnn.nnn.nnn.nnn -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Note that /var/lib/mysql/mysql.sock is the socket associated with the main instance, not the alternates. It seems that if I *specify* a port, then mysql ought to use that port. It only seems to be an issue when not specifying a --host, or when using the value 'localhost'. It's a bit terrifying because during all my testing, I am thinking I am connected to the correct instance, when in fact, I was not. It will be very easy to blow away the main instance data by mistake. Can I somehow convince the mysql command line client to use the specified parms, rather than the (incorrect) socket? Thank you. Regards, Rich > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 12, 2006 6:00 PM > To: mysql@lists.mysql.com > Subject: Re: MySql Error Number 1130 > > When you are trying to connect to port 3307, for example, are > you specifying that port from your remote machine? Or are you > connecting to the mysqld listening on port 3306? > > You could try shutting down the server listening on port > 3306, and then connecting to port 3307. See if the error > message changes or goes away. > > shell># mysql -h host_name -u root -pxxxxx --port=3307 > > I think you may need to specify the absolute IP address in > the user table, instead of a wildcard '%'. > > See if this helps. Use your root username and password in > place of 'tommy'. I did not want to mess up my root user > account! Use the IP address of your remote machine you want > to connect to mysql with, in place of 10.0.0.5. > > mysql> create user 'tommy'@'10.0.0.5' > -> identified by '12345'; > Query OK, 0 rows affected (0.00 sec) > > mysql> grant all on *.* > -> to 'tommy'@'10.0.0.5' > -> identified by '12345'; > Query OK, 0 rows affected (0.00 sec) > > mysql> select * from user where user = 'tommy' \G > ************** 1. row ***************** > Host: 10.0.0.5 > User: tommy > Password: xxxxxxxx snipped > Select_priv: Y > Insert_priv: Y > Update_priv: Y > Delete_priv: Y > Create_priv: Y > Drop_priv: Y > Reload_priv: Y > Shutdown_priv: Y > Process_priv: Y > File_priv: Y > Grant_priv: N > References_priv: Y > Index_priv: Y > Alter_priv: Y > Show_db_priv: Y > Super_priv: Y > Create_tmp_table_priv: Y > Lock_tables_priv: Y > Execute_priv: Y > Repl_slave_priv: Y > Repl_client_priv: Y > Create_view_priv: Y > Show_view_priv: Y > Create_routine_priv: Y > Alter_routine_priv: Y > Create_user_priv: Y > ssl_type: > ssl_cipher: > x509_issuer: > x509_subject: > max_questions: 0 > max_updates: 0 > max_connections: 0 > max_user_connections: 0 > 1 row in set (0.00 sec) > > HTH > > Keith > > In theory, theory and practice are the same; in practice they are not. > > On Wed, 12 Apr 2006, Duzenbury, Rich wrote: > > > To: mysql@lists.mysql.com > > From: "Duzenbury, Rich" <[EMAIL PROTECTED]> > > Subject: MySql Error Number 1130 > > > > Hi all, > > > > I am setting up two additional instances of mysql on my > mysql server, > > which is running version 5.0.18-standard-log. > > > > I've got the additional instances set up, and they are > running. I can > > see that they are bound to the proper ports. > > > > I can connect to them locally like > > mysql -p --port=3306 > > mysql -p --port=3307 > > mysql -p --port=3320 > > > > I have always been able to connect remotely from my > workstation to the > > base server on 3306. > > > > My problem is that I cannot connect remotely to either of the new > > additional instances running on 3307 or 3320. Anytime I > attempt to do > > so, I receive MySQL Error Number 1130, Host > 'nnn.nnn.nnn.nnn' is not > > allowed to connect to this MySQL server. > > > > I've been googling and reading manuals all day, and haven't > made a dent. > > > > > > User Table > > *************************** 1. row *************************** > > Host: % > > User: root > > Password: (redacted) > > Select_priv: Y > > Insert_priv: Y > > Update_priv: Y > > Delete_priv: Y > > Create_priv: Y > > Drop_priv: Y > > Reload_priv: Y > > Shutdown_priv: Y > > Process_priv: Y > > File_priv: Y > > Grant_priv: Y > > References_priv: Y > > Index_priv: Y > > Alter_priv: Y > > Show_db_priv: Y > > Super_priv: Y > > Create_tmp_table_priv: Y > > Lock_tables_priv: Y > > Execute_priv: Y > > Repl_slave_priv: Y > > Repl_client_priv: Y > > ssl_type: > > ssl_cipher: > > x509_issuer: > > x509_subject: > > max_questions: 0 > > max_updates: 0 > > max_connections: 0 > > *************************** 2. row *************************** > > Host: localhost > > User: root > > Password: (redacted) > > Select_priv: Y > > Insert_priv: Y > > Update_priv: Y > > Delete_priv: Y > > Create_priv: Y > > Drop_priv: Y > > Reload_priv: Y > > Shutdown_priv: Y > > Process_priv: Y > > File_priv: Y > > Grant_priv: Y > > References_priv: Y > > Index_priv: Y > > Alter_priv: Y > > Show_db_priv: Y > > Super_priv: Y > > Create_tmp_table_priv: Y > > Lock_tables_priv: Y > > Execute_priv: Y > > Repl_slave_priv: Y > > Repl_client_priv: Y > > ssl_type: > > ssl_cipher: > > x509_issuer: > > x509_subject: > > max_questions: 0 > > max_updates: 0 > > max_connections: 0 > > > > > > db Table: > > *************************** 1. row *************************** > > Host: % > > Db: mysql > > User: root > > Select_priv: Y > > Insert_priv: Y > > Update_priv: Y > > Delete_priv: Y > > Create_priv: Y > > Drop_priv: Y > > Grant_priv: Y > > References_priv: Y > > Index_priv: Y > > Alter_priv: Y > > Create_tmp_table_priv: Y > > Lock_tables_priv: Y > > > > Host table is empty. > > > > Here is one of the processes: > > mysql 5972 5960 0 16:17 pts/0 00:00:00 /usr/sbin/mysqld > > --no-defaults - > > -port=3307 --socket=/srv/mysql/lx07/mysql.sock > > --pid-file=/srv/mysql/lx07/lx09.p > > id07 --datadir=/srv/mysql/lx07/data --log=/srv/mysql/lx07/mysql.log > > --skip-locki ng --key_buffer=256M --max_allowed_packet=16M > > --table_cache=256 --sort_buffer_si ze=16M --read_buffer_size=8M > > --read_rnd_buffer_size=4M --myisam_sort_buffer_size =64M > > --thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8 > > --tmpdi r=/tmp/ --innodb_file_per_table > > --innodb_data_home_dir=/srv/mysql/lx07 > > --innodb_ > > data_file_path=ibdata1:100M:autoextend > > --innodb_log_group_home_dir=/srv/mysql/lx > > 07/ --innodb_log_arch_dir=/srv/mysql/lx07/ > > --innodb_buffer_pool_size=128M --inno > > db_additional_mem_pool_size=20M --innodb_log_file_size=32M > > --innodb_log_buffer_s ize=8M --innodb_flush_log_at_trx_commit=1 > > --innodb_lock_wait_timeout=50 > > > > I have made sure to flush privileges, and I've restarted the server > > several times, to no avail. I am running out of things to try, and > > hoping someone can see my (what is most likely) obvious error. > > > > Thank you. > > > > Regards, > > Rich > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
