Linux Server Details

User Tools

Site Tools

Trace:
mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
mysql [2022/07/19 19:52] – created 85.134.209.200mysql [2022/07/20 22:15] (current) – Updated formatting admin
Line 1: Line 1:
 ====== MySQL ====== ====== MySQL ======
  
-======Common MYSQL Commands======+=====Common MYSQL Commands=====
 Update information in a Table: Update information in a Table:
 +<code>
  update table set email='email@test.com' where ref_no='x';  update table set email='email@test.com' where ref_no='x';
 +</code>
  
 Alter the Structure of a Table: Alter the Structure of a Table:
 +<code>
  alter table abstract_submit modify surname varchar(250);  alter table abstract_submit modify surname varchar(250);
  alter table paper_submit add (abstract text);  alter table paper_submit add (abstract text);
- alter table paper_submit drop primary key; <nowiki>//</nowiki>dont worry, this will only drop the primary key and leave the data and column intact.+ alter table paper_submit drop primary key; #dont worry, this will only drop the primary key and leave the data and column intact. 
 +</code>
  
 Some databases may not provide the user with "All Privileges". This line adds the "Alter" permission to the user.: Some databases may not provide the user with "All Privileges". This line adds the "Alter" permission to the user.:
 +<code>
  GRANT ALTER ON db.* TO db@localhost IDENTIFIED BY 'db_pass';  GRANT ALTER ON db.* TO db@localhost IDENTIFIED BY 'db_pass';
 +</code>
  
-**MYSQL USERS ADD/ REMOVE** +=====MYSQL USERS ADD/ REMOVE===== 
 +<code>
 Add/ Create a New User: Add/ Create a New User:
  GRANT ALL PRIVILEGES  GRANT ALL PRIVILEGES
Line 20: Line 26:
  TO faim@'%'  TO faim@'%'
  IDENTIFIED BY 'pass';  IDENTIFIED BY 'pass';
 +</code>
  
 Delete a User: Delete a User:
 +<code>
  DELETE FROM USER WHERE USER='name';  DELETE FROM USER WHERE USER='name';
  drop user user@localhost;  drop user user@localhost;
 +</code>
  
 Remove a Users Privileges: Remove a Users Privileges:
 +<code>
  REVOKE ALL PRIVILEGES  REVOKE ALL PRIVILEGES
  ON *.*   ON *.* 
  FROM davisc@'%';  FROM davisc@'%';
 +</code>
  
 =====MYSQLDUMP===== =====MYSQLDUMP=====
-Backup MySQL Database:<br>+Backup MySQL Database: 
 +<code>
  mysqldump -h localhost -u user -p db_name > db_backup.sql  mysqldump -h localhost -u user -p db_name > db_backup.sql
 +</code>
  
 If you get the following error, you may have to "skip lock tables": If you get the following error, you may have to "skip lock tables":
 +<code>
  mysqldump: Got error: 1044: Access denied for user 'dbuser'@'localhost' to database 'db_name' when using LOCK TABLES  mysqldump: Got error: 1044: Access denied for user 'dbuser'@'localhost' to database 'db_name' when using LOCK TABLES
 +</code> 
 +<code>
  mysqldump -h localhost -u db_user -p --lock-tables=false db > db_backup.sql  mysqldump -h localhost -u db_user -p --lock-tables=false db > db_backup.sql
- <nowiki>//</nowiki>Another method:+ #Another method:
  mysqldump -h localhost -u db_user -p --skip-lock-tables db > db_backup.sql  mysqldump -h localhost -u db_user -p --skip-lock-tables db > db_backup.sql
 +</code>
 +
 +===== Importing a MySQL Dump =====
 +<code>
 +mysql -u username -p database_name < file.sql
 +</code>
  
 ===== Setting and Inputting Fixed Variables in MySQL Statements ===== ===== Setting and Inputting Fixed Variables in MySQL Statements =====
 +<code>
  mysql> set @a=1;  mysql> set @a=1;
  Query OK, 0 rows affected (0.05 sec)  Query OK, 0 rows affected (0.05 sec)
    
  mysql> insert into t values(@a);  mysql> insert into t values(@a);
 +</code>
  
 ===== Show colums as Rows ===== ===== Show colums as Rows =====
 The following switch \G will display information in Groups of Rows instead of Columns. Example: The following switch \G will display information in Groups of Rows instead of Columns. Example:
 +<code>
  mysql> use mysql  mysql> use mysql
  mysql> show table status;  mysql> show table status;
- <nowiki>//</nowiki>Its quite hard to read and has many many columns. Now try:+ #Its quite hard to read and has many many columns. Now try:
  mysql> show table status\G  mysql> show table status\G
- <nowiki>//</nowiki>no semi-colon needed. All information is shown in Rows. + #no semi-colon needed. All information is shown in Rows. 
 +</code>
 ====== Block all MySQL Users Temporarily ====== ====== Block all MySQL Users Temporarily ======
 +<code>
  SHOW VARIABLES LIKE 'max_connections';  SHOW VARIABLES LIKE 'max_connections';
  SET GLOBAL max_connections = 0;  SET GLOBAL max_connections = 0;
 +</code>
  
 The above locks all people out from the MySQL server. The above locks all people out from the MySQL server.
Line 63: Line 89:
 **"Although setting the variable to a value of 0 suggests that no connections are allowed, one connection is actually reserved for the root user."** **"Although setting the variable to a value of 0 suggests that no connections are allowed, one connection is actually reserved for the root user."**
  
-To see if there are any current connections or processes runing:+To see if there are any current connections or processes running: 
 +<code>
  SHOW PROCESSLIST;  SHOW PROCESSLIST;
 +</code>
  
 ====== Determine Logged in users and their Connections to the DB ====== ====== Determine Logged in users and their Connections to the DB ======
 +<code>
  mysqladmin processlist  mysqladmin processlist
- <nowiki>//</nowiki>The above assumes that the mysql root password in stored in .my.cnf+ #The above assumes that the mysql root password in stored in .my.cnf 
 +</code>
  
-===== Script to log MySQL users and persistant connections =====+===== Script to log MySQL users and persistent connections ===== 
 +<code>
  #!/bin/bash  #!/bin/bash
  mysqladmin processlist | gzip > /var/tmp/mysqlconnlog$(date +%d%m%y_%H%M%S).gz  mysqladmin processlist | gzip > /var/tmp/mysqlconnlog$(date +%d%m%y_%H%M%S).gz
  
- <nowiki>//</nowiki>Create a 5min cron job:+ #Create a 5min cron job:
  */5 * * * * /root/logmysqlconn.sh  */5 * * * * /root/logmysqlconn.sh
 +</code>
  
 ====== Repair a Crashed MySQL Table ====== ====== Repair a Crashed MySQL Table ======
 In case you stumble across an error like: "Table 'table_name' is marked as crashed and should be repaired" and/or specifically with gallery2 you recieve "ERROR_STORAGE_FAILURE", you'll need to do the following: In case you stumble across an error like: "Table 'table_name' is marked as crashed and should be repaired" and/or specifically with gallery2 you recieve "ERROR_STORAGE_FAILURE", you'll need to do the following:
 +<code>
  mysql -u username -p databasename  mysql -u username -p databasename
  check table g2_SessionMap;  check table g2_SessionMap;
  repair table g2_SessionMap;  repair table g2_SessionMap;
 +</code>
 If you enable debug in gallery2 ( $gallery->setDebug(true); ), you'll see the above table needing a repair.  If you enable debug in gallery2 ( $gallery->setDebug(true); ), you'll see the above table needing a repair. 
  
 ====== Set limits for MySQL usage by Users ====== ====== Set limits for MySQL usage by Users ======
 To limit a user/database from overloading a MySQL server, the following can be done. To limit a user/database from overloading a MySQL server, the following can be done.
 +<code>
  mysql   #log into mysql as root  mysql   #log into mysql as root
  show grants for user@'193.1.99.64/255.255.255.192';  show grants for user@'193.1.99.64/255.255.255.192';
Line 98: Line 132:
  grant usage on kartblog.* to kartblog@'localhost' with max_user_connections 5;  grant usage on kartblog.* to kartblog@'localhost' with max_user_connections 5;
  show grants for kartblog@'localhost';  show grants for kartblog@'localhost';
- <nowiki>//</nowiki>Not sure what max_user_connections is ideal, but will try it at 5.+ #Not sure what max_user_connections is ideal, but will try it at 5. 
 +</code>
  
-References: +References:\\ 
-http://dev.mysql.com/doc/refman/5.0/en/user-resources.html +http://dev.mysql.com/doc/refman/5.0/en/user-resources.html\\ 
-http://dev.mysql.com/doc/refman/5.0/en/show-grants.html+http://dev.mysql.com/doc/refman/5.0/en/show-grants.html\\
  
 ====== Export a MySQL select query ====== ====== Export a MySQL select query ======
 So I was using mysql> via the command line, and I wanted to export a large select query to a text file. Here is how: So I was using mysql> via the command line, and I wanted to export a large select query to a text file. Here is how:
 +<code>
  echo "select distinct pl_title from pagelinks;" | mysql wikidb > mediawiki-complete-page-listing-urls.sql  echo "select distinct pl_title from pagelinks;" | mysql wikidb > mediawiki-complete-page-listing-urls.sql
 +</code>
 Reference: http://www.commandlinefu.com/commands/view/817/export-mysql-query-as-.csv-file Reference: http://www.commandlinefu.com/commands/view/817/export-mysql-query-as-.csv-file
  
Line 114: Line 151:
 http://dev.mysql.com/doc/refman/5.0/en/mysql.html http://dev.mysql.com/doc/refman/5.0/en/mysql.html
  
-http://e-mats.org/2010/04/error-1577-hy000-at-line-1-cannot-proceed-because-system-tables-used-by-event-scheduler-were-found-damaged-at-server-start/  <br> +http://e-mats.org/2010/04/error-1577-hy000-at-line-1-cannot-proceed-because-system-tables-used-by-event-scheduler-were-found-damaged-at-server-start/  -> Upgrade issue when tailing daemon.logs after upgrading from mysql-5 to mysql-5.1
-^ Upgrade issue when tailing daemon.logs after upgrading from mysql-5 to mysql-5.1+
mysql.1658260370.txt.gz · Last modified: 2022/07/19 20:13 (external edit)