Category Archives: Database

Backup and Restore many mysql database in one shot

Its my bad for not reading database manual that lead me for doing boring task .

Dump a few database from server and dumping it in my windows xp. one by one.

Perhaps someone who see this post can learn and not doing same mistake as me.

Command to backup one database :

backup database pendidikan :

edp# mysqldump -ualam -p pendidikan > pd.sql

backup database cms_study :

edp# mysqldump -ualam -p cms_study> cms1.sql

backup database cms_stmik :

Continue reading

Diff database structure with sqlyog

When requirement for information stored in database grow, old table structure need to adjust for flexibility.

Common task to do in  normal cycle :

1. Build new application in other place

2. Synchronize database ( tables)

3. Upload application.

Second step ( synchronize tables) often  painful when a lot of changes made at home and need to sync with server environment.

A handy tool to sync database table I often use is sqlyog.

For example I have two database :

db1 (at server)

db2 (at home)

table structure has changed.

In db1, table testing has this fields :

CREATE TABLE `testing` (
`id` tinyint(3) NOT NULL auto_increment,
 `name` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
 Continue reading 

Installing your favorite mysql version in FreeBSD

Ports offer more flexibility than package in general but when dealing with size I prefer using package :-)

My common question when see this on freebsd ports page :

to get mysql support on php, I must install mysql-client-5.0.41 thats means I must install mysql5 server too if I decide to install mysql in my server, I prefer to install mysql4. Is it possible?

I’m luckly, freebsd ports offer flexible options, simple trick to use mysql 4.0.27 with php4 is installing mysql4 server first.

after installing mysql server version 4 then php4-mysql will use mysql4-client automatically.

with this option some options for favorit mysql version available, depend on you.

-bash-2.05b$ cd /usr/ports/databases/
-bash-2.05b$ ls | grep mysql

Continue reading

Some mysql sql basic command I must remember

I use phpmyadmin in daily environment, do I still know  sql command?

My sql file too large, it’s hard to import using phpmyadmin, please help me

Some question around mysql from my student, my false make them spoil using phpmyadmin all day long :-)

Here’s my answers :

1. Command to create database

create database databasename

2. Delete database

drop database databasename

3. Import database

mysql -uusername -ppassword databasename < databasename.sql

4. Export database

mysqldump -uusername -ppassword databasename > databasename.sql

5. Pick database and choose one to manage

use databasename

6. List all table in database

show tables

 Illustration in mysql command line :

Continue reading

Insert data when empty and update it when data exist

With so many function provided in mysql plus lazy, It seem like finding gem for replace my old approach :-)

duplicate data isn’t good, I must search for exisiting data, in general : insert data to table when no data with that value exist and update table if data exist

For example :

I have table A with field :

1. id, integer 11 primary key auto increment

2. name, varchar 25

3. address, varchar 100

4. phone, varchar 20

5. username, varchar 10 unique

a common operation to insert data is :

Checking for data, ex :

$check_data = mysql_query("select * from A where username =’$user’");
if(mysql_num_rows($check_data)>0) {
do update data
mysql_query("update A set bla..bla..bla");
}
else {
insert query here
mysql_query("insert into A bla..bla..bla");
}

after find "replace()"

mysql_query("replace into A (field1,field2)  values(‘values1′,’values2’)")

Yes, I still need to do research for another ‘glitch’ possibility in current application.

From mysql manual :

As long index exist for that table, like primary key or unique for helping mysql to find duplicate data or not.

Yes, replace don’t insert please  😉