Database – Page 4 – Pario TechnoBlob

Linux

Copying One mySQL database from one machine to another

# mysqldump -h host.com -u root -p password sourceDB | mysql -h host2.com -u username -p password -C targetDB Interestingly, you don’t actually have be on the ‘source’ machine thanks to mySQL’s -h switch. From the target machine, simply specify the remote host after the -h. This was quite useful for me as my source […]

Scripting

mySQL and Stored Triggers

This is an example on how you an use stored triggers in mySQL /* test_workaround.sql */ USE test ; DROP TABLE IF EXISTS `addresses`; CREATE TABLE `addresses` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `created_on` DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00’, `updated_on` DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00′, `mac_address` CHAR(17) NOT NULL DEFAULT ’00:16:DD:xx:xx:xx’, /* […]

Linux

mySQL search and replace

UPDATE ´tablename´ set field = replace(field, ’searchString’, ‘replaceString’);

Linux

Set mySQL Auto Increment manually

ALTER TABLE table_name AUTO_INCREMENT = 100000

Scripting

MDB tools to export (migrate) from mdb (Microsoft Access format) to mySQL

This convertion could be obtained by first converting the tables in the mdb-file into CSV-files (Comma Separated Values) using MDB Tools and then by importing the csv-files into MySQL using phpMyAdmin. To export a given table from a mdb-file, we type: mdb-export db.mdb customers >> customers.csv In my case, since I got some danish characters, […]