Results 1 to 5 of 5

Thread: MySQL Issues

  1. #1

    Default MySQL Issues

    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. #2

    Default Re: MySQL Issues

    -----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. #3

    Default Re: MySQL Issues

    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. #4
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: MySQL Issues

    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.
    Technology is 'stuff that doesn't work yet.' -- Bran Ferren

  5. #5
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: MySQL Issues

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

    Regarding your /etc/my.cnf file:

    Code:
    [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).
    Technology is 'stuff that doesn't work yet.' -- Bran Ferren

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •