Difference between revisions of "MySQL Server"

From KnowledgeNerd
Jump to navigation Jump to search
(Created page with "== Linux Command Line Commands For MySQL Server == <pre> install mysql database ................. $sudo apt-get install mysql-server install mysql client ......................")
 
 
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 ====

Latest revision as of 20:53, 8 September 2019

Linux Command Line Commands For MySQL Server

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

Preferred commands with some Linux: 

service mysql start
service mysql stop
service mysql restart
or
service mysqld start
service mysqld stop
service mysqld restart

Mac OS X Command Line Commands For MySQL Server

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

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: 

<!--?xml version="1.0" encoding="UTF-8"?-->
<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>

Then update permissions and add it to launchctl: 

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

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. 

sudo launchctl remove com.mysql.mysqld
sudo launchctl remove com.mysql.mysqld.plist

mysql Tool Command Line Commands

connect local mysql server ............. $mysql -u root -p

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: -N, --skip-column-names | Don't write column names in results.

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. Query OK, 0 rows affected (0.00 sec), 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.
SELECT * FROM information_schema.processlist
WHERE USER = 'drupal' AND DB = 'drupal6' AND TIME > 200;
  • 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.
SELECT CONCAT('KILL ', ID, ';') FROM information_schema.processlist
WHERE USER = 'drupal' AND DB = 'drupal6' AND TIME > 200;
  • 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.
SELECT CONCAT('KILL ', ID, ';') FROM information_schema.processlist
WHERE USER = 'drupal' AND DB = 'drupal6' AND TIME > 200 INTO OUTFILE '/tmp/sql-kill-stuck.txt';
  • Execute commands from the created file
SOURCE /tmp/sql-kill-stuck.txt;

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.
$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);
  }
}

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

Reset mysql root password

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

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): 

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 2

Backup Database

Restore Database

MySQL Server Logs

Located e.g. in /var/www/drupal/ (files: mysql.log, mysql-slow.log)

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

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 max_allowed_packet is big enough in /etc/my.cnf: 

[mysqld]
max_allowed_packet=64M

Restart MySQL server to apply changes. If you can't find the mysql config file, check 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 SHOW TABLES; 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 

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

To get the full "Create view" code, click "Print view (with full texts)" 

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

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: 

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;

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

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`

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

Create a Stored Procedure

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

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. 

ALTER {PROCEDURE | FUNCTION} {proc_name | func_name} [characteristic ...]
  characteristic: SQL SECURITY {DEFINER | INVOKER}| COMMENT 'string'

Drop a Stored Procedure

DROP PROCEDURE IF EXISTS exampleSproc;

Variables in Sprocs

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

or 

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

Parameters in Sprocs

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

Misc Sprocs

Remove Emails Sproc

Stored procedure which removes wanted email addresses from newsletter tables 

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 ;

Example call: 

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

MySQL Datatypes

Text Types

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.

Number Types

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.

Serial

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

Date Types

DATE		YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	e.g. YYYYMMDDHHMMSS (basicly same as datetime)
TIME		HH:MM:SS
Retrieved from "https://knowledgenerd.net/index.php?title=MySQL_Server&oldid=24"