Here’s a few tricks that required more than a few minutes of Googling to uncover:
insert into tbl_blah (user_id, thing_id) ( select other_user_id, other_thing_id from tbl_other where some_criteria = other_criteria )
Finding rows with dodgey data (in this case, rogue line feeds):
select text from tbl_blah where text like concat( "%", char(10), "%")
To replace characters when retrieving data:
select replace( replace (text, "\r\n", " " ), ",", "" ) text from tbl_blah
To permanently fix up the data in the table:
update tbl_blah set text = replace( replace ( text, "\r\n", " " ), ",", "" )
The above example eliminates carriage returns that were added with Windows, as well as commas. Note: Mac/Unix carriage returns will be different. See this article for more info: http://www.xaprb.com/blog/2006/04/14/bad-character-data-in-mysql
The char() function can also be used in the replace() function, but unfortunately values over 127 will not work if the collation doesn’t support it. :-(
select hex(response_text ) from tbl_blah where blah_id = 9999
Note: the corresponding PHP function is bin2hex().
To stop the server:
mysqladmin -uroot shutdown
or, on OS X:
sudo /Library/StartupItems/MySQLCOM stop
MySQLCOM is a script, so lots of juicy stuff in there! Although, having said that, it actually relies heavily on another script to do most of the heavy lifting:
To start the server, assuming you are using the installer from the MySQL web site:
sudo /Library/StartupItems/MySQLCOM start
And nary a my.cnf file was to be found, so to the last resort he turned - modifying the following file:
and modifying line ~200 as follows:
$bindir/mysqld_safe --datadir=$datadir --timezone=UTC --pid-file=$amp;pid_file >/dev/null 2>&1 &
Taken from this Stack Overflow question: http://stackoverflow.com/questions/225772/compare-two-mysql-databases.
If you're working with small databases I’ve found running mysqldump on both databases with the --skip-comments and --skip-extended-insert options to generate SQL scripts, then running diff on the SQL scripts works pretty well.