Difference between revisions of "Mysql notes"

From Simson Garfinkel
Jump to navigationJump to search
 
(2 intermediate revisions by the same user not shown)
Line 5: Line 5:
   alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"
   alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"


==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==
==Tuning MySQL==
Line 31: Line 42:
; Useful admin commands
; Useful admin commands
   mysqladmin shutdown
   mysqladmin shutdown
==MacOS Server==
MySQL is built in. I find it easier to use Apple's provided server, rather than building my own:
To restart the server (for some reason, launchctl doesn't stop the MySQL server):
  mysqladmin shutdown
  sudo launchctl start org.mysql.mysqld


==Linux==
==Linux==
Restart MySQL:
Restart MySQL:
   sudo /etc/init.d/mysql restart
   sudo /etc/init.d/mysql restart

Latest revision as of 05:42, 14 January 2018

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"

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