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:
- http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
- http://www.mysqlperformanceblog.com/
General
- Useful admin commands
mysqladmin shutdown
Linux
Restart MySQL:
sudo /etc/init.d/mysql restart