Tag: mysql

Last rowcount in MySQL

#mysql

Suppose 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_table WHERE whatever; SELECT FOUND_ROWS(); This will return the number of rows in the last select query (or if the first query has a LIMIT clause, ... Read more

20 Sep 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You 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 GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUse... Read more

20 Jul 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

PROBLEM 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='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | ... Read more

20 Jun 2016 - 1 minute read

MySQL regexp Replace

#mysql

MySQL 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 text which affects multiple records such as obsolete URL, spelling mistake and such. The syntax of REPLACE function is as follows: UPDATE tbl_name S... Read more

20 Feb 2016 - 1 minute read

MySQL partial import

#mysql #import

Here’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). Because MySQL dumps are simply SQL scripts, we can use sed in order to extract only the portion of the SQL instructions we want. Let say the name of ... Read more

20 Sep 2015 - less than 1 minute read

Moving MySQL datafiles

#mysql #linux

In 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 to lure Mysql into thinking the files are still in place. mkdir /new_dir/datafiles/my_db cd /old_dir/datafiles/ cp -Rvp my_db/* /new_dir/datafiles/my_... Read more

20 May 2015 - less than 1 minute read

Check database uptime

#database #oracle #mysql

Sometimes 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 database SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') AS "DB Startup Time" FROM sys.v_$instance; On Mysql database SHOW GLOBAL STATUS LI... Read more

20 Mar 2015 - less than 1 minute read

Running Mysql commands from Bash script

#mysql #linux

Eventually, 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 the date or whatever fits your needs: $ cat execmysql.sh #!/bin/sh ids="3,4" table="NMS.main" qry="select id,data from $table where id in ($ids)" echo ... Read more

20 Sep 2014 - less than 1 minute read

Create a read-only MySQL user

#mysql

Sometimes 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 the database. Here’s how to provide only read privilege to a user: grant select on database_name.* to 'username'@'%' identified by 'userpasswd'; grant... Read more

20 Aug 2014 - less than 1 minute read

MySQL transaction isolation

#database #mysql #transaction

You 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 UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} -- Prepare session SET autocommit =... Read more

20 Jun 2014 - less than 1 minute read

Using SCP with file compression

#mysql #linux

The 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 few axamples with different results: To copy & compress in a single line gzip -c test_arch.arc | ssh user@new_serv "cat > /home/oracle/backup/b... Read more

20 Apr 2014 - less than 1 minute read

Resolving the “ERROR 126 Incorrect key file” error

#database #mysql

The 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 on /var/tmp while MySQL was trying to create a temporary table to resolve a complex query. The generation of temporary tables can be caused by derive... Read more

20 Mar 2014 - 1 minute read

Data export/import on MySQL

#database #mysql #import #export

MySQL 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 following command will export a whole MySQL database on a sql file: mysqldump -u user DB_NAME > /tmp/file.sql To export a single table The followin... Read more

20 Feb 2014 - less than 1 minute read

Check all tables on MySQL

#database #mysql

Data 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 --all-databases Read more

20 Jan 2014 - less than 1 minute read