MySQL Issues

English Applications
1

Hi everyone,

New to the forums and a bit new to linux/opensuse and running servers in general so my questions might have a bit of a simple solution to them!

I’ve moved my database to a new server since then I’m having a bit of trouble connecting, I can get the following error:

Not connected : Access denied for user ‘root’@‘localhost’ (using password: YES)

Yet if I used root@xxx.xxx.xxx.xxx (external server ip masked) I can connect fine with the same login credientials.

Also sometimes when I run a bulk load of INSERT queries I now end up with the following error:

2006: MySQL server has gone away

Here’s the contents of my.cnf if that is any help?

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

[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql-bin
binlog_format=mixed

server-id = 1
set-variable = net_buffer_length=2G
set-variable = max_allowed_packet=2G
set-variable = myisam_sort_buffer_size=2G
set-variable = sort_buffer=2G
set-variable = table_cache=100
set-variable = query_cache_size=2147483648
set-variable = max_connections=10000
set-variable = key_buffer=2G
big-tables

[safe_mysqld]
log-error = /var/log/mysql/mysqld.log
socket = /var/run/mysql/mysql.sock

!include_dir /etc/mysql

[mysqldump]
socket = /var/run/mysql/mysql.sock
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysqld_multi.log

Thanks for any help!
Adam

2

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Look up MySQL’s documentation on users and permissions. MySQL handles
users based on their username, where they originate (localhost, public
IP, somewhere on the Internet, etc.), and password. MySQL behaves this
way on all platforms.

Good luck.

Want to yell at me in person?
Come to BrainShare 2011 in October: http://tinyurl.com/brainshare2011
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.15 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQIcBAEBAgAGBQJOglfEAAoJEF+XTK08PnB5FBQQALYTr0CTUKBxFrSftfA9SmhE
weYRTIaRq4Cyr7kxnN/iVI2KLPL01dZzWpFfVwRAJbAVU/rtMm02ROUJPqYHRKgL
GzpxunsuLu2tmAoRnU7G6XSymAGW8/RmIYZ8rCV3FY0CJw1hkvMNQP218wPTKc5V
52hX6ISDLvoNRjhOICkgc+5s/Vs4GfW4mdkaxiPfUSULZVdY8LpNjPNETcZ67PYJ
TDtqF8f3ED9Sla3rv/LrbSzE6GSh5RdQYWYrEQwjKbXepXE+oWzAH7CXkBUxRlOz
aIhK5kEuPmp8ZPBk73PqlcNjID6p/buvcToBznJgzPEsaMcQa6YtfAstI2QJWlmz
j8pwCh9tWhkecpr8tKYDvVXqdAzSUfH4QX5l6u3zYsAfKST/j5kKUSo9DP5z9W22
2kxaT5bViO1lAm/FfRXnNajlH/qA5RtcDwoD4QaaBNQ1alvijF6gUTNdT2yhftZA
F5/uLXkgZesUoUs1Fyx7oJ34G6EeRhOkyYqx4BJNkTfIpNRatV0QqTMpqP9oINzB
7q6h0AsbQ38+v6Aw8w0sRpY0H+havIPQsmfmVRd12pg1Nhc9xDcR+jYmaTGVeYgC
pK8SzbvVxmleN3U/NT000qyA77iOlG5fc2crx/YxCHniI0wEYn6GQgJRMKuuUO+H
IgpkaGqH0EUrWCFl07uK
=9GnL
-----END PGP SIGNATURE-----

3

The previous poster was correct, you need to grant privileges to the user AND hosts.

To get you started, have a look at:

mysql> select User,Host from mysql.user;

mysql> show grants for ‘root’@‘localhost’;

mysql> GRANT ALL PRIVILEGES ON database.* TO ‘user’@‘192.168.x.y’ IDENTIFIED BY ‘password’;

These are just some pointers, there are many great tutorials on MySQL available.

Welcome to the forum and have fun with Linux!

Cheers,
Pete

4

I’ve moved my database to a new server

That triggers a bunch of questions:

  1. How did you “move” the db?

  2. What are the versions of mysql (old and new server)?

  3. Did you move the “mysql” database as well?

Quite some features of MySQL have changed over time. One of them is the password format. The safe way to do a migration is to set up the new MySQL server from the repo packages, adding the permissions from scratch and then importing databases from a dump. Watch out for the character set collations as well. openSUSE has changed the default from latin1 to UTF8.

5

Please have a look at the file /var/log/mysql/mysqld.log
Any errors should be logged there.

Regarding your /etc/my.cnf file:

[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql-bin
binlog_format=mixed

server-id = 1
set-variable = net_buffer_length=2G
set-variable = max_allowed_packet=2G
set-variable = myisam_sort_buffer_size=2G
set-variable = sort_buffer=2G
set-variable = table_cache=100
set-variable = query_cache_size=2147483648
set-variable = max_connections=10000
set-variable = key_buffer=2G
big-tables

How much RAM do you have in your server? Your key_buffer_size is probably too small. And please check the manual for the correct syntax of the my.cnf file (red color above).