Tag Archives: MySQL

View Progress Of MySQL Dump Restore Or Import

Posted by Peter on May 7, 2011 None comments

Ever wondered why the MySQL command line tool don’t have any sort of progress information or status update? Well, you’re not alone if you do. Especially importing a GB sized dump file can take a long time, especially if the table engine was InnoDB.

There are some clever utilities which can be used to accomplish just that, by giving the user simple, yet useful information about the progress of their process. Let’s look at them.

Bar (Command Line Progress Bar)

The Bar utility, or in full terms Command Line Progress Bar can be downloaded from Sourceforge. If you are running Ubuntu, it’s as simple as running ‘sudo apt-get install bar’ to install it. You then simply pipe your MySQL import and get a nice status bar.

shell> bar -if=mysql_db_data.sql | mysql
Bar utility screenshot

Read more »

Cross deleting records from multiple tables in mysql

Posted by Peter on December 21, 2010 None comments

I’ve been asked many times how to delete records in multiple tables in MySQL. Here are couple of methods that should get anyone started.

Simple Inner Join Method

The simple inner join method uses commas with an implicit inner join like in the example below. Here we’re using an items and price table, where each table has an itemId field which is what we join them on.

DELETE i.*, p.*
FROM items i, price p
WHERE i.itemId = p.itemId
AND i.itemId = 101

Traditional Inner Join Method

In the traditional inner join we’re keeping the join statement clear of the where clause, but it works as well. Whatever personal preference you may have. Note that you can delete individual table records from one table only, if you require to do so.

DELETE i.*, p.*
FROM items i
INNER JOIN price p
ON i.itemId = p.itemId
WHERE i.itemId = 101

Read more »

WITH (NOLOCK) table hint equivalent for MySQL

Posted by Peter on September 6, 2010 2 comments

I don’t remember how many times I was asked about an equivalent term of the infamous “NOLOCK” hint for mysql database server, hence I thought it was worth to write about it here. “WITH (NOLOCK)” is a transaction isolation levels that defines how data is available during an update, or with other words it is a property that defines at what point changes made by an update operation will become available in a row, table or database to other processes.

The official SQL standard defines four isolation levels:

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE

Oracle, SQL Server and MySQL support isolation levels. During an operation, the database engine places certain locks to maintain data integrity. Different types of locking apply to different databases (Oracle vs. MySQL), or table types (eg. MyISAM vs. InnoDB).

When WITH (NOLOCK) is used with SQL Server, the statement does not place a lock nor honor exclusive locks on table. The MySQL equivalent is READ UNCOMMITTED, also known as “dirty read” because it is the lowest level of isolation. If we specify a table hint then it will override the current default isolation level. MySQL default isolation level is REPEATABLE READ which means locks will be placed for each operation, but multiple connections can read data concurrently. Read more »

How to find duplicate rows in a MySQL database table

Posted by Peter on April 22, 2010 None comments

I’ve been asked the question “How can I return duplicate rows only from a MySQL db table” so many times already, that I’ve decided to post it here in a short article.

It is not something intuitive or readily available (at least it seems), but the solution is short and very simple.

While this query:

SELECT DISTINCT column1
FROM table1

gives us all records without the duplicates, this one returns only the duplicate ones:

SELECT DISTINCT column1
FROM table1
GROUP BY column1
HAVING COUNT(column1) > 1

And by increasing the having count, you can retrieve records with multiple occurrences.




		

	

		

		
MySQL – How To Analyze, Repair and Optimize all Tables

		

			
				Posted by Peter				on November 2, 2009
							
			None comments
		

		

		

			
Ever come across a situation, where you’d like to check all tables in a database and have them all repaired and optimized? My guess is yes.


In case you didn’t know, there is a helpful MySQL utility called mysqlcheck, available as of version 3.23.38. It does exactly what we need.


To check all tables in all databases for corruption and errors and also fix them in one go, this is your command:


mysqlcheck -u username -p password  –check –optimize –auto-repair –all-databases


mysqlcheck executes statements like CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE and chooses the best statements for any given operation and storage engine.


Note that the operations complete a lot faster if you can afford to to disable any external services, especially if your database is large.



		

	

		

		
How to install MySQL Server, PHP and Apache on a Mac

		

			
				Posted by Peter				on October 24, 2009
							
			1 comments
		

		

		

			
These instructions lead you thru the installation of the latest LAMP Stack on MacOS X Snow Leopard.


1 – Download the installation image from MySQL website here. Then double-click to mount and open the disk image.


2 – Install MySQL Server by double clicking the package “mysql-5.1.*****.pkg” and follow the menu, accepting the default values, unless you want to change something and know exactly what you’re doing.


3 – Install MySQL Startup Item by double-clicking the package “MySQLStartupitem.pkg” and follow the menu.


4 – Install MySQL Preference Pane by double-clicking the file “MySQL.prefPane” and follow the menu. This item will simplify the management of your SQL Server. You can now use the “System Preferences” panel to start and stop the database server.


5 – Enable the php module in your apache config file. You might know that Snow Leopard already ships with Apache 2.2 and PHP 5.3, but it needs a couple of tweaks to make it work smoothly. So, open /etc/apache2/httpd.conf and search for “php5_module”. Remove the comment (#) in front of the line, save and close the file, then restart apache (sudo apachectl restart)


 Read more »



		

	

		

		
Setup MySQL Replication the easy way

		

			
				Posted by Peter				on August 14, 2009
							
			2 comments
		

		

		

			
1. Configure the Master


We will need to modify a file called my.cnf, which is the main configuration file for mysql. On most systems it’s located in /etc/ or /etc/mysql/ and it contains all important configuration data.


First, let’s ensure that networking is enabled and mysql listens on all, or at least the client’s IP addresses. We also have to tell mysql what file to write the logs to and from which databases to keep logging, so that the Slave can pick up the changes. And finally we need to assign a unique ID to the Master.


All this info is contained within the following lines in your my.cnf file. Please note that the position of these entries can be spread throughout the file, so you might have to search for each of them.


#skip-networking


# bind-address = xxx.xxx.xxx.xxx (this can be the Slave’s IP address. if you’re not sure, leave it commented out)


log-bin = /var/log/mysql/mydatabase-bin.log


server-id = 1


 


Restart the server. Then log into mysql and create a user with replication privileges:


GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@'%’ IDENTIFIED BY ‘<password>’;

FLUSH PRIVILEGES; 


USE mydatabase;


The next 3 steps is to lock all tables on the database, take a backup and get the replication sequence ID. We’ll use the backup later on the Slave to establish the baseline, and tell it to start replication starting from the sequence ID.


 Read more »



		

	

		

		
INSERT IF NOT EXISTS in MySql

		

			
				Posted by Peter				on June 3, 2009
							
			5 comments
		

		

		

			
INSERT IGNORE is the syntax that does mimic INSERT IF NOT EXISTS, as there is no direct command as stated in the title, at least not in the current release of MySql.

The statement INSERT IGNORE (and to some extent REPLACE INTO) does essentially the same thing, inserting a record if that given record does not exists.
See the following samples:







 Read more »