Creator: /* MySQL Server Administration */

2019-09-08T19:53:04Z

MySQL Server Administration

Creator: Created page with "== Linux Command Line Commands For MySQL Server ==

 install mysql database ................. $sudo apt-get install mysql-server install mysql client ......................"

2019-09-08T19:04:43Z

Created page with "== Linux Command Line Commands For MySQL Server == <pre> install mysql database ................. $sudo apt-get install mysql-server install mysql client ......................"

New page

== Linux Command Line Commands For MySQL Server ==

<pre>
install mysql database ................. $sudo apt-get install mysql-server
install mysql client ................... $sudo apt-get install mysql-client

start mysql server ..................... $service mysql start
start mysql server ..................... $service mysqld start
start mysql server ..................... $/etc/init.d/mysql start
start mysql server ..................... $/etc/init.d/mysqld start

stop mysql server ...................... $/etc/init.d/mysql stop
restart mysql server ................... $/etc/init.d/mysql restart
reload mysql server configurations ..... $/etc/init.d/mysql reload
reload mysql server configurations ..... $/etc/init.d/mysql force-reload
check the status of mysql server ....... $/etc/init.d/mysql status
</pre>

Preferred commands with some Linux:
<pre>
service mysql start
service mysql stop
service mysql restart
or
service mysqld start
service mysqld stop
service mysqld restart
</pre>

== Mac OS X Command Line Commands For MySQL Server ==

<pre>
start mysql server ..................... $sudo /usr/local/mysql/support-files/mysql.server start
stop mysql server ...................... $sudo /usr/local/mysql/support-files/mysql.server stop
restart mysql server ................... $sudo /usr/local/mysql/support-files/mysql.server restart
reload mysql server settings ........... $sudo /usr/local/mysql/support-files/mysql.server reload
force-reload mysql server settings ..... $sudo /usr/local/mysql/support-files/mysql.server force-reload
check the status of mysql server ....... $sudo /usr/local/mysql/support-files/mysql.server status
</pre>

=== Start MySQL Server at System Startup ===

To start MySQL server automatically on MAC OS X system startup, create a property list file (.plist)
sudo vim /Library/LaunchDaemons/com.mysql.mysql.plist
...and add the following lines to it:
<pre>

<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true />
<key>Label</key>
<string>com.mysql.mysqld</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/mysql/bin/mysqld_safe</string>
<string>--user=mysql</string>
</array>
</dict>
</plist>
</pre>

Then update permissions and add it to launchctl:
<pre>
sudo chown root:wheel /Library/LaunchDaemons/com.mysql.mysql.plist
sudo chmod 644 /Library/LaunchDaemons/com.mysql.mysql.plist
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysql.plist
</pre>

=== Disable MySQL Server at System Startup ===

sudo rm /Library/LaunchDaemons/com.mysql.mysql.plist

Other way (which didn't work for me) is to search the service name:
sudo launchctl list | grep -i mysql
and remove it e.g.
<pre>
sudo launchctl remove com.mysql.mysqld
sudo launchctl remove com.mysql.mysqld.plist
</pre>

== mysql Tool Command Line Commands ==

<pre>
connect local mysql server ............. $mysql -u root -p
</pre>

=== Execute a Query To DB With mysql Tool ===

Create a new database:
mysql -u root -p -e 'CREATE DATABASE drupal6_bu;'

Show all PhpBBForum tables (starting with 'phpbb_') from Drupal6 database:
mysql -u root -p -e 'USE Drupal6; SHOW TABLES;' | grep 'phpbb_'
or with different query and writing results into a file:
mysql -u root -p -N information_schema -e "SELECT table_name FROM TABLES WHERE table_name LIKE 'phpbb_%' AND table_schema LIKE 'Drupal6'" > phpbb_tables.txt

* Note: <code>-N, --skip-column-names | Don't write column names in results.</code>

=== Kill Stuck Processes ===

Connect remote mysql server (you have to have mysql client installed on current computer):
mysql -u root -p -h 123.12.1.1

Show ongoing processes:
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

Kill stuck processes (using the id of wanted process):
KILL 8494378;

Even it says e.g. <code>Query OK, 0 rows affected (0.00 sec)</code>, the process was quite sure killed. Check if the process have disappeared:
SHOW PROCESSLIST;

=== Kill Multiple Stuck Processes ===

MySQL doesn't have a query to kill multiple processes and you have to do it one by one. There's still couple handy ways to do that.

'''From MySQL Server Command Line'''

* Prepare a wanted sql query, e.g.
<pre>
SELECT * FROM information_schema.processlist
WHERE USER = 'drupal' AND DB = 'drupal6' AND TIME > 200;
</pre>

* '''Option 1''': When you have all the processes as results which you want to terminate, execute following query, copy and clean the output and execute it.
<pre>
SELECT CONCAT('KILL ', ID, ';') FROM information_schema.processlist
WHERE USER = 'drupal' AND DB = 'drupal6' AND TIME > 200;
</pre>

* '''Option 2''': When you have all the processes as results which you want to terminate, execute following query and save kill statements to a file.
<pre>
SELECT CONCAT('KILL ', ID, ';') FROM information_schema.processlist
WHERE USER = 'drupal' AND DB = 'drupal6' AND TIME > 200 INTO OUTFILE '/tmp/sql-kill-stuck.txt';
</pre>

* Execute commands from the created file
<pre>
SOURCE /tmp/sql-kill-stuck.txt;
</pre>

'''From PHP-Script'''

* Following is only an example, it's good to narrow the result set with WHERE statement so you don't kill e.g. possible replication threads.
<pre>
$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
$process_id=$row["Id"];
if ($row["Time"] > 200 ) {
$sql="KILL $process_id";
mysql_query($sql);
}
}
</pre>

== MySQL Server Administration ==

=== MySQL Server Configuration File ===

You can find it by typing:
find -name my.cnf

It can locate e.g. in:
/etc/mysql/my.cnf

==== Enable MySQL Server Slow Query Log ====

MySQL has a feature that allows you to log slow running queries to a file. To enable you just need to add some lines to your my.cnf file, and restart. To log the queries taking longer than two seconds to the specified log file, add the following (or uncomment lines if exists):
<pre>
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
</pre>

=== Backup Database ===

* [[Mysqldump |Backup database using mysqldump tool]]
* [[SQL#Backup_database_using_phpMyAdmin_web_ui |Backup database using phpMyAdmin web ui ]]

=== Restore Database ===

* [[SQL#Restore_Database]]

== MySQL Server Logs ==

Located e.g. in /var/www/drupal/ (files: mysql.log, mysql-slow.log)
* http://dev.mysql.com/doc/refman/5.1/en/server-logs.html

== MySQL Server Tips ==

=== Check MySQL Server Version ===

mysql --version
// prints e.g. mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper

=== If MySQL Server is stuck and website using it is down ===

* Click Server/Palvelin --> Processes/Prosessit
* Choose Kill/Lopeta for processes which are stuck
* (You can see processes also from (but can't kill them): Check ongoing queries/locks from processlist (Database: information_schema - Table: PROCESSLIST) e.g. with phpMyAdmin)
* If there's a lot of locked queries for certain table (e.g. phpbb_sessions) you could try to change table type e.g. from MyISAM to InnoDB
* If there's a lot of queries which are increasing, you can stop your webserver to prevent generating more queries
* Restart MySQL server if there's something wrong with the queries

==== Kill MySQL Processes ====

Besides phpMyAdmin, you can kill processes by typing:
KILL thread_id

* Check: [[MySQL_Server#Kill_Stuck_Processes]]
* More information from: http://dev.mysql.com/doc/refman/5.1/en/kill.html

=== MyISAM vs. InnoDB ===

Claims for different database engines. Claims aren't surely facts.

==== MyISAM ====

'''Pros'''
* Is faster for applications like phpbb
* Uses simpler structure thus takes less space and has better performance with simple queries
* Much faster for large text based primary keys
* You can use mysqlhotcopy which is faster than mysqldump
* MyISAM supports FULL-TEXT indexes while InnoDB does not

'''Cons'''
* Supports only table locking
* No support for transactions
* No data integrity (e.g. relationship constraints) check, responsibility is left e.g. for database administrators and application developers.

==== InnoDB ====

'''Pros'''
* You can use inno hot backup, which don't lock the database during backup (writes ahead log). InnoDB Hot Backup has been renamed to MySQL Enterprise Backup, which is included as part of the MySQL Enterprise Edition.
* Supports row locking
* Give better performance results in inserting and updating data records
* Uses clustered indexes to reduce IO operations for PK access
* Has transactions while MyISAM does not
* Has better crash recovery
* (InnoDB is newer while MyISAM is older)

'''Cons'''
* No FULL-TEXT support
* Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.

==== Conclusions ====

* If your project is small and doesn't have any issues with DB leave MyISAM
* If you have write-heavy load (like community site, where users add many content items) choose InnoDB

==== Change Table Engine From MyISAM to InnoDB And Vice Versa ====

To change a MyISAM table to an InnoDB table:
ALTER TABLE table_name ENGINE = InnoDB;

To convert an InnoDB table to a MyISAM table:
ALTER TABLE table_name ENGINE = MyISAM;

* Tip: When converting from MyISAM to InnoDB you can speed up the transformation. Before converting the table, adjust order so that the primary key column is in order:
ALTER TABLE tablename ORDER BY 'primary_key_column';

* This will pre-arrange the table so that it can be converted quickly without a lot of re-arranging required in MySQL. Then, simply change the table engine:
ALTER TABLE tablename ENGINE = INNODB;

== MySQL Tips ==

=== Select inside update query ===

If you get following error message:
#1093 - You can't specify target table 'gsmvalidation' for update in FROM clause

When executing following query:
UPDATE `gsmvalidation` SET confirmedtime = (SELECT NOW()) WHERE id = (SELECT id FROM `gsmvalidation` WHERE uid = 1 ORDER BY `codesended` DESC LIMIT 1)

The reason is that in MySQL, you can't modify the same table which you use in the SELECT part. You have to make another subquery and give alias for table name.

Solution:
UPDATE `gsmvalidation` SET confirmedtime = (SELECT NOW())
WHERE id = (SELECT id FROM (SELECT id,uid,codesended from `gsmvalidation`) AS temp WHERE uid = 1 ORDER BY `codesended` DESC LIMIT 1)

=== MySQL server has gone away error ===

If you get the following error while restoring a big database:
ERROR 2006 (HY000) at line ##: MySQL server has gone away

..make sure that the value <code>max_allowed_packet</code> is big enough in /etc/my.cnf:
<pre>
[mysqld]
max_allowed_packet=64M
</pre>
Restart MySQL server to apply changes. If you can't find the mysql config file, check [[Config_files | Config files page]]

=== After Restoring Mysql Database Phpmyadmin Won't Show The Tables ===

* Problem: I exported a production database and created a development database from the backup. Now when I try to check the contents of the database from phpmyadmin, there's no tables in it. I can see the tables with <code>SHOW TABLES;</code> command and from mysql command line but not from phpmyadmin.

* Possible reason: The imported database has VIEW(S) defined by a user that don't exist in the new computer's mysql users.

* Solution: Create a missing user or drop the view(s) and create them again with an existing user.

* Case: Exporting and importing Drupal 6 database with Ubercart module

Drupal 6 database includes 3 views when Uber Cart module is installed:
dr_uc_order_products_pair_vw, dr_uc_order_products_qty_vw, dr_uc_order_products_user_vw

Check the information and creation script of the specific view
<pre>
SHOW CREATE VIEW dr_uc_order_products_pair_vw
Results:
View: dr_uc_order_products_pair_vw
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`drupal`@`www.p...
character_set_client: utf8
collation_connection: utf8_general_ci
</pre>

To get the full "Create view" code, click "Print view (with full texts)"
<pre>
CREATE ALGORITHM=UNDEFINED DEFINER=`drupal`@`www.yoursite.com` SQL SECURITY DEFINER VIEW `dr_uc_order_products_pair_vw` AS select `op1`.`nid` AS `nid`,`op2`.`nid` AS `pair_nid`,sum(`op2`.`qty`) AS `pair_sum_qty`,count(`op2`.`nid`) AS `order_count` from (`dr_uc_order_products` `op1` join `dr_uc_order_products` `op2`) where ((`op1`.`order_id` = `op2`.`order_id`) and (`op1`.`nid` <> `op2`.`nid`)) group by `op1`.`nid`,`op2`.`nid` order by `op1`.`nid`,count(`op2`.`nid`) desc,sum(`op2`.`qty`) desc
</pre>

If you try to check the contents of the view:
SELECT * FROM dr_uc_order_products_pair_vw
You'll get an error message:
#1449 - The user specified as a definer ('drupal'@'www.yoursite.com') does not exist

Now you can drop the views causing problem and recreate those with the right users:
<pre>
DROP VIEW dr_uc_order_products_pair_vw;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `dr_uc_order_products_pair_vw` AS select `op1`.`nid` AS `nid`,`op2`.`nid` AS `pair_nid`,sum(`op2`.`qty`) AS `pair_sum_qty`,count(`op2`.`nid`) AS `order_count` from (`dr_uc_order_products` `op1` join `dr_uc_order_products` `op2`) where ((`op1`.`order_id` = `op2`.`order_id`) and (`op1`.`nid` <> `op2`.`nid`)) group by `op1`.`nid`,`op2`.`nid` order by `op1`.`nid`,count(`op2`.`nid`) desc,sum(`op2`.`qty`) desc;
</pre>

== Views ==

=== List Views ===

Phpmyadmin shows different icon (eye) for views in table listing compared to normal tables.
You can get a list of all views in specific database with a sql query
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

=== Check Views ===

Shows the properties of a view and it's creation statement
SHOW CREATE VIEW test_view

=== Create Views ===

<pre>
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `test-table`.`id` AS `id`,`test-table`.`age` AS `age` from `test-table`
</pre>

..or from phpmyadmin, execute a sql query for wanted result set and click "Create View" link from bottom section of the page.

=== Delete Views ===

DROP VIEW test_view

=== Replace Views ===

When creating a new view from phpmyadmin you can specify an existing name and check the "OR REPLACE" box.

== Stored Procedures and Functions ==

Since MySQL version 5.0 there have been a support for stored procedures, triggers and events. Stored procedures are saved in mysql database's proc table.
* http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

=== Create a Stored Procedure ===

<pre>
DELIMITER //
CREATE PROCEDURE GetAllArticles()
BEGIN
SELECT * FROM articles;
END //
DELIMITER ;
</pre>

=== Call the Stored Procedure ===

CALL DatabaseName.StroredPrecedureName();

=== Alter a Stored Procedure ===

Note! With the ALTER PROCEDURE Statement you can change only the characteristics and if you want to change in statement list then you have to DROP the procedure and CREATE again.
<pre>
ALTER {PROCEDURE | FUNCTION} {proc_name | func_name} [characteristic ...]
characteristic: SQL SECURITY {DEFINER | INVOKER}| COMMENT 'string'
</pre>

=== Drop a Stored Procedure ===

DROP PROCEDURE IF EXISTS exampleSproc;

=== Variables in Sprocs ===

* Save count of articles into a variable and print it
<pre>
DELIMITER //
CREATE PROCEDURE HowMany()
BEGIN
DECLARE articlecount INT DEFAULT 0;
SELECT COUNT(*) INTO articlecount FROM articles;
SELECT articlecount;
END //
DELIMITER ;
</pre>

or

<pre>
DELIMITER //
CREATE PROCEDURE HowMany()
BEGIN
DECLARE articlecount INT DEFAULT 0;
SET articlecount = 33;
SELECT CONCAT('Article count: ',articlecount) AS 'End Result';
END //
DELIMITER ;
</pre>

=== Parameters in Sprocs ===

<pre>
DELIMITER $$
CREATE PROCEDURE testdatabase.ShowArticles(IN emails VARCHAR(255))
BEGIN
SELECT * FROM users WHERE email IN (emails);
END $$
DELIMITER ;
</pre>

* http://www.mysqltutorial.org/stored-procedures-parameters.aspx

=== Misc Sprocs ===

==== Remove Emails Sproc ====

Stored procedure which removes wanted email addresses from newsletter tables

<pre>
DELIMITER //
DROP PROCEDURE IF EXISTS drupal6.removeSubscribers//
CREATE PROCEDURE drupal6.removeSubscribers(IN emailsToRemove VARCHAR(2000))
BEGIN
SELECT REPLACE(emailsToRemove,' ','') INTO emailsToRemove;
SELECT REPLACE(emailsToRemove,',','\',\'') INTO emailsToRemove;
SELECT CONCAT('\'', emailsToRemove, '\'') INTO emailsToRemove;
SET @sql1 = CONCAT('DELETE FROM simplenews_snid_tid WHERE snid IN (SELECT snid FROM simplenews_subscriptions WHERE mail IN (', emailsToRemove, ')) LIMIT 200');
SET @sql2 = CONCAT('DELETE FROM simplenews_subscriptions WHERE mail IN (', emailsToRemove, ') LIMIT 200');
PREPARE stmt1 FROM @sql1;
PREPARE stmt2 FROM @sql2;
EXECUTE stmt1;
EXECUTE stmt2;
SELECT CONCAT('Email addresses removed: ',ROW_COUNT()) AS 'Affected Rows';
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
#SELECT @sql1;
END //
DELIMITER ;
</pre>

Example call:
CALL drupal6.removeSubscribers('email1@yahoo.com,email2@hotmail.com, email3@google.com');

== MySQL Datatypes ==

=== Text Types ===
<pre>
CHAR( ) A fixed section from 0 to 255 characters long.
VARCHAR( ) A variable section from 0 to 255 characters long, e.g. VARCHAR(20)
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
BLOB A string with a maximum length of 65535 characters.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.
</pre>

=== Number Types ===

<pre>
TINYINT( ) -128 to 127 normal
0 to 255 UNSIGNED.
TINYINT(1) Boolean. 0 is FALSE and others TRUE.
SMALLINT( ) -32768 to 32767 normal
0 to 65535 UNSIGNED.
MEDIUMINT( ) -8388608 to 8388607 normal
0 to 16777215 UNSIGNED.
INT( ) -2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED.
BIGINT( ) -9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 UNSIGNED.
FLOAT A small number with a floating decimal point.
DOUBLE( , ) A large number with a floating decimal point.
DECIMAL( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.
</pre>

==== Serial ====

<code>SERIAL</code> is an alias for <code>BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE</code>. Good option e.g. for first ID-column.

=== Date Types ===

<pre>
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP e.g. YYYYMMDDHHMMSS (basicly same as datetime)
TIME HH:MM:SS
</pre>

← Older revision		Revision as of 19:53, 8 September 2019
Line 166:		Line 166:
	/etc/mysql/my.cnf		/etc/mysql/my.cnf

		+	=== Reset mysql root password ===
		+
		+	<pre>
		+	service mysql stop
		+	mysqld_safe --skip-grant-tables &
		+	# Log into MySQL server
		+	mysql
		+	# Replace NEW-PASS-HERE with desired new root password
		+	UPDATE mysql.user SET Password=PASSWORD('NEW-PASS-HERE') WHERE User='root';
		+	FLUSH PRIVILEGES;
		+	exit;
		+	mysqladmin -u root -p shutdown
		+	service mysql start
		+	</pre>

	==== Enable MySQL Server Slow Query Log ====		==== Enable MySQL Server Slow Query Log ====

MySQL Server - Revision history

Creator: /* MySQL Server Administration */

Creator: Created page with "== Linux Command Line Commands For MySQL Server ==
install mysql database ................. $sudo apt-get install mysql-server install mysql client ......................"