Mysql notes

From Simson Garfinkel
Jump to navigationJump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Notes on MySQL:

I generally store my MySQL password in an environment variable (like MYSQL_PASSWORD) and then have all of the mysql commands aliased to use the password, e.g.:

 alias mysql="mysql -p$MYSQL_PASSWORD"
 alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"

However, the modern way to do this is to store in a local defaults file such as:

   % cat defaults.ini
   [client]
   username=myusername
   password=mypassword
   port=myport
   host=myhost
   Then specify this file with:
   MySQL --defaults-extra-file=defaults.ini

Here is a query to size all of the tables:

   SELECT table_schema "DB Name",
      ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
   FROM information_schema.tables 
   GROUP BY table_schema; 

Creating a new user for a new database

   MariaDB [(none)]> CREATE DATABASE forensics wiki;
   Query OK, 1 row affected (0.00 sec)
   MariaDB [(none)]> CREATE USER 'fwiki'@'localhost' IDENTIFIED BY 'password';
   Query OK, 0 rows affected (0.09 sec)
   MariaDB [(none)]> GRANT ALL ON forensicswiki.* to 'fwiki'@'localhost';
   Query OK, 0 rows affected (0.06 sec)
   MariaDB [(none)]> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.05 sec)
   MariaDB [(none)]> ^D
   Bye

Tuning MySQL

Look at the system load. If mysqld is not consuming 100% of the system load, then it is disk-bound, not CPU bound. You need to increase the buffers.

In the file my.cnf, try editing these values and making them larger until the system is CPU bound:

 key_buffer              = 8192M
 innodb_buffer_pool_size = 1024M
 sort_buffer_size = 100M
 read_buffer_size = 100M
 read_rnd_buffer_size = 100M
 myisam_sort_buffer_size = 100M
 query_cache_size = 1024M
 # Try number of CPU's*2 for thread_concurrency
 thread_concurrency = 8

Try these commands:

 mysql> SHOW INNODB STATUS\G

See also:

General

Useful admin commands
 mysqladmin shutdown

Linux

Restart MySQL:

 sudo /etc/init.d/mysql restart