sometimes we need to backup a database or transport a database from one server to another. the situations are not always good. you are stranded with no phpmyadmin help. mysql provide mysqldump to help you in non-gui environment. just do:
% mysqldump [name_of_the_database] -u [username] -p > [name_of_the_database].sql
as example,
% mysqldump krs -u krs_user -p > krs.sql
the process is finish in few minutes depends on the size of the databases. here you are the backup process is finish. to transport to other server, just copy the krs.sql to the directed server, than do:
% mysql krs < krs.sql -u krs_user -p
or see my previous post about mysql.
Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts
Monday, August 27, 2007
Monday, July 16, 2007
mysql update & insert from file
Maintaining Enrolment system is not an pleasant task. So many things must be reminded as bad documentation was supplied. Hehehe blame on myself that was too lazy to write them down.
This is just notes from me to rectify those problems above:
In order to update the data by increasing or decreasing its value, you do not need to use select then update. Instead you only need update query. See the example below:
While, inserting data from main database server to local database [in my case: I need to copy data from main uni's database into faculty's database, so my proposed solution is to read from main uni's database using php then run a script that contains command below. To make the process run automatically, I will put it as cron jobs]
Inserting data into employee_data table with employee.dat file
On Windows
1). Move the file to c:\mysql\bin.
2). Make sure MySQL is running.
3). Issue the following command
mysql employees < employee.data
On Linux
1). Migrate to the directory that contains the downloaded file.
2). Issue the following command
mysql employees < employee.data -u theUser -p
3). Enter your password.
Example of employee.dat:
Sources from the tips above with little modification: http://www.webdevelopersnotes.com
This is just notes from me to rectify those problems above:
In order to update the data by increasing or decreasing its value, you do not need to use select then update. Instead you only need update query. See the example below:
UPDATE employee_data SET
salary = salary + 20000,
bonus = bonus - 5000
WHERE title='CEO';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
While, inserting data from main database server to local database [in my case: I need to copy data from main uni's database into faculty's database, so my proposed solution is to read from main uni's database using php then run a script that contains command below. To make the process run automatically, I will put it as cron jobs]
Inserting data into employee_data table with employee.dat file
On Windows
1). Move the file to c:\mysql\bin.
2). Make sure MySQL is running.
3). Issue the following command
mysql employees < employee.data
On Linux
1). Migrate to the directory that contains the downloaded file.
2). Issue the following command
mysql employees < employee.data -u theUser -p
3). Enter your password.
Example of employee.dat:
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("John", "Hagan", "Senior Programmer", 32, 4, 120000, 25000, "john_hagan@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Ganesh", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g_pillai@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Anamika", "Pandit", "Web Designer", 27, 3, 90000, 15000, "ana@bignet.com");
Sources from the tips above with little modification: http://www.webdevelopersnotes.com
Subscribe to:
Posts (Atom)