Tag: mysql
Last rowcount in MySQL
#mysqlSuppose you want to count the number of lines returned by the last SQL statement issued. For select statements you can use the FOUND_ROWS construct: SELECT SQL_CALC_FOUND_ROWS something FROM your_... Read more
20 Sep 2016 - less than 1 minute readMySQL punch user creation DDL
#mysqlYou can get the DDL necessary to duplicate an existing user with the following system command: MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRA... Read more
20 Jul 2016 - less than 1 minute readInnoDB table rebuild
#mysqlPROBLEM On a MySQL 5.6 database server, the ibdata1 file includes 5 InnoDB tables in the mysql schema. mysql> select table_name from information_schema.tables -> where table_schema='mys... Read more
20 Jun 2016 - 1 minute readMySQL regexp Replace
#mysqlMySQL provides you with a useful string function called REPLACE that allows you to replace a string in a column of a table by a new string. The REPLACE function is very handy to search and replace ... Read more
20 Feb 2016 - 1 minute readMySQL partial import
#mysql #importHere’s the situation. You have a dumpfile called mysql.dmp which contains a whole MySQL database dump. However you only need to import one table (or anything, but only a portion of that dumpfile). ... Read more
20 Sep 2015 - less than 1 minute readMoving MySQL datafiles
#mysql #linuxIn this article, I’ll demonstrate multiple solutions to move datafiles Method 1: using symbolic links Stop the Mysql instance. /etc/init.d/mysql stop Then move the files and put symbolic links ... Read more
20 May 2015 - less than 1 minute readCheck database uptime
#database #oracle #mysqlSometimes you have to check when a database was started for the last time. To get the information, just log on to the database and use the following query to get the last startup time: On Oracle d... Read more
20 Mar 2015 - less than 1 minute readRunning Mysql commands from Bash script
#mysql #linuxEventually, you’ll need to automate queries on your Mysql database, let’s say, for reporting purposes. Hee’s how to put SQL queries into a shell script, and possibly including variables based on th... Read more
20 Sep 2014 - less than 1 minute readCreate a read-only MySQL user
#mysqlSometimes you just need to give access to some users of your database. As you don’t want theses users to modify or accidentally delete some data, it’s advisable to grant them only read access to th... Read more
20 Aug 2014 - less than 1 minute readMySQL transaction isolation
#database #mysql #transactionYou can get session-level and global transaction isolation levels using these commands : SELECT @@global.tx_isolation; SELECT @@tx_isolation; SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL ... Read more
20 Jun 2014 - less than 1 minute readUsing SCP with file compression
#mysql #linuxThe command-line tool SCP can be effectively used to move files and perform file compression in the transfer. However, there’s different ways to achieve this, with different results. Here are a fe... Read more
20 Apr 2014 - less than 1 minute readResolving the “ERROR 126 Incorrect key file” error
#database #mysqlThe Incorrect key file error: ERROR 126 (HY000) at line 3: Incorrect key file for table '/var/tmp/#sql3f5_1b6c4e_1.MYI'; try to repair it This error probably means that you ran out of disk space... Read more
20 Mar 2014 - 1 minute readData export/import on MySQL
#database #mysql #import #exportMySQL provides you with a lot of flexibility when it comes to import or export data between databases. Let’s examine a few possibilities to export/import data: To export a whole database The foll... Read more
20 Feb 2014 - less than 1 minute readCheck all tables on MySQL
#database #mysqlData corruption is every DBA’s worst nightmare and can happen anytime. Use this command to check and repair all tables on a MySQL database : mysqlcheck -u root -p --auto-repair --check --optimize ... Read more
20 Jan 2014 - less than 1 minute read