Mysql notes - Revision history

Simson at 08:29, 26 October 2023

2023-10-26T08:29:21Z

@@ Line 4: / Line 4: @@
    alias mysql="mysql -p$MYSQL_PASSWORD"
    alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"
 ==Creating a new user for a new database==

Simson: /* Creating a new user for a new database */

2018-01-14T12:42:33Z

Creating a new user for a new database

← Older revision		Revision as of 05:42, 14 January 2018
Line 14:		Line 14:
	MariaDB [(none)]> FLUSH PRIVILEGES;		MariaDB [(none)]> FLUSH PRIVILEGES;
	Query OK, 0 rows affected (0.05 sec)		Query OK, 0 rows affected (0.05 sec)
	MariaDB [(none)]> Bye		MariaDB [(none)]> ^D
			Bye

	==Tuning MySQL==		==Tuning MySQL==

Simson: /* =Creating a new user for a new database */

2018-01-14T12:41:07Z

=Creating a new user for a new database

← Older revision		Revision as of 05:41, 14 January 2018
Line 5:		Line 5:
	alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"		alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"

	==Creating a new user for a new database=		==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';		MariaDB [(none)]> CREATE USER 'fwiki'@'localhost' IDENTIFIED BY 'password';
	Query OK, 0 rows affected (0.09 sec)		Query OK, 0 rows affected (0.09 sec)

Simson at 12:37, 14 January 2018

2018-01-14T12:37:31Z

@@ Line 4: / Line 4: @@
    alias mysql="mysql -p$MYSQL_PASSWORD"
    alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"
@@ Line 31: / Line 40: @@
 ; Useful admin commands
    mysqladmin shutdown
 ==Linux==
 Restart MySQL:
    sudo /etc/init.d/mysql restart

Simson: /* Linux */

2009-12-12T18:34:40Z

Linux

← Older revision		Revision as of 11:34, 12 December 2009
Line 42:		Line 42:

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

Simson: /* Tuning MySQL */

2009-12-12T18:33:11Z

Tuning MySQL

← Older revision		Revision as of 11:33, 12 December 2009
Line 12:		Line 12:
	key_buffer = 8192M		key_buffer = 8192M
	innodb_buffer_pool_size = 1024M		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:		Try these commands:

Simson: Created page with '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.: …'

2009-12-12T18:29:56Z

Created page with '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.: …'

New page

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"

==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

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

==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==