Mysql – Enabling remote access to thesql on debian

debianMySQLSecurity

I need to enable remote access for a specific user on a mysql server, running debian oldstable.

Bear in mind I'm no SQL expert nor a real sysadmin.

Logging in phpmyadmin and looking at the Privileges page, I notice two odd things:

an "Any" user, with host %, no password, and global USAGE privilege. No idea what does that mean or imply. If I "edit" that user, I see it has a lot of privileges on a table called test and on a test\_%, whatever that means – please notice I have no such tables in my database, at least they do not appear in the phpmyadmin list.
root both have a line for "localhost" host, and for "127.0.0.1" host.

Point 2 is fine, just odd, seems a bit redundant and senseless to me. Point 1 is what worries me, what am I supposed to do about that?

Best Answer

In response to your questions.

The "Any" user and the "test" database are used by mysql during installation to check that the database was installed properly. You can safely remove both after the installation.

I am unsure about the redundancy in the root user "localhost" and "127.0.0.1". Mysql probably just does that to be safe.

To allow remote access, you will need to edit the file /etc/mysql/my.cnf to make mysql bind to external interfaces. By default, mysql will only accept connections from localhost for security reasons. Follow the instructions given here.

After that, create a new user and give it the user's ip or '%' for all ips and restart mysql. The remote user should now be able to connect to the mysql instance.

Related Solutions

MySQL privileges – DROP tables, not databases

I have a same problem :( I think if user can drop all tables then empty database is no value

MySQL DB causing high IO running Zabbix server

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

Best Answer

Related Solutions

MySQL privileges – DROP tables, not databases

MySQL DB causing high IO running Zabbix server

Related Topic