Hi,

Personally i think, that Linux terminal is the strongest application/tool whatever you name it. Yesterday i was restoring a MySQL database size of 5MB via MySQL Administrator application, and it crashed several times. Actually i use it because the easy way to have GUI with few clicks, but that doesn’t mean i never used terminal for such things. But this crashing made me think of this powerful tool to use and use it forever..

So lets have backup our database 1st via terminal.

1- The 1st thing you need to do is open terminal(konsole).

2- mysqldump will be used to backup the database we want to.

mysqldump -u Username -p  databasename > backup.sql

Username = this is your database username
databasename = the name of your database which you want to backup
backup.sql = the filename for your database backup

The text in red should be changed according to your specific MySQL/database details. You can also give path to the filename where you want to save the backup file like: /home/mian/backup.sql.

Now lets run it in real form:

mysqldump -u admin -p storedb > /home/mian/storedb.sql

By entering this command and hitting Enter key will ask you MySQL password for user admin. By doing so will backup storedb database to the path given.

Now lets restore storedb database.

1- I assume that terminal is open.

2- Run this command in terminal:

mysql -u admin -p storedb < /home/mian/storedb.sql

This command is not much different except we don’t have mysqldump this time. Which means we are not dumping any database, just to restore one. It will also ask for the password for user admin to proceed.

If you want to run bash script to do the job for you by cron at specific times, i had written this script in past, but didn’t get time to update it regularly, but still is enough to do the backup archives with no manual running from command line.

Have fun!

(No Ratings Yet)

 Loading ...

Popularity: 3%

Hi all,

Do you want to create a separate user for a database which only some users/user can access. Want to create user who can only have access to databases you assign them. Then this tutorial is for you.

This will be a very simple tutorial and howto about how to create a database 1st, a user and then assign some privileges to the user to allow him/her to perform some specific actions like insert,create,update,select etc etc.

I assume you are on Linux and command shell (terminal) is opened.

To enter to MySQL mode in terminal type this:

mian@liveserver:~$ mysql -u root -p

Just enter the red color text from above, or copy from here will work and upon asking for the password enter your root password (not distro one, MySQL root password). -u means username and -p is password.

You will see output like this:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35614
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Lets create a database (if you don’t have yet) with any name you desire (use text in red):

mysql> create database store;

You will see this output:

Query OK, 1 row affected (0.00 sec)

Now the fun part, lets create a user with password and assign some basic privileges on a database we created above:

mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on store.* to asim@localhost identified by ‘storeadminasim‘;

In the above command you need to change the following according to your need.

store represents the database we created above.

asim is the user we want to create for the database store.

storeadminasim is the password assigned to asim.

Text in dark blue above shows the privileges we want to assign to user asim, in this case he will just perform that actions on a database.  We will not let him do whatever he want, this is good for security. This user will only access this database assigned to him and nothing else, and he can create tables in it, can update it, insert data into tables etc etc. If the above command runs successfully you will see this output in terminal:

Query OK, 0 rows affected (0.00 sec)

Now the last and important thing is to flush the privileges, type this (text in red):

mysql> flush privileges;

You will see this output:

Query OK, 0 rows affected (0.00 sec)

That’s it, you can now quit/exit from MySQL session:

mysql> exit
Bye

The user and database is created and ready for use. Got any questions?, use the comment form to ask.

Arif

(+1 rating, 1 votes)

 Loading ...

Popularity: 4%

Hi folks,

Some time we need to have a CSV file and store our database data in it for reporting and other things. To create such file on the fly we will need PHP, Apache and MySQL installed which i am sure is installed. What this script will be capable of:

• Get data from MySQL table
• Export multiple rows from db
• Save file with different file name with date & time

Here is the script. One would need to connect to database and the rest of explanation i write in the comments inside the script to explain the lines.

<?php
//db.php connection to your database

// Query to get results from mysql table
$query = “SELECT id,name,phone,address FROM user”;
$res = mysql_query($query);

// Functions for export to excel.
function xlsBOF() {
echo pack(“ssssss”, 0×809, 0×8, 0×0, 0×10, 0×0, 0×0);
return;
}
function xlsEOF() {
echo pack(“ss”, 0x0A, 0×00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack(“sssss”, 0×203, 14, $Row, $Col, 0×0);
echo pack(“d”, $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack(“ssssss”, 0×204, 8 + $L, $Row, $Col, 0×0, $L);
echo $Value;
return;
}
// Unique file name with datetime
$file_name = “csv-”.date(‘dmy-His’);
header(“Pragma: public”);
header(“Expires: 0″);
header(“Cache-Control: must-revalidate, post-check=0, pre-check=0″);
header(“Content-Type: application/force-download”);
header(“Content-Type: application/octet-stream”);
header(“Content-Type: application/download”);;
header(“Content-Disposition: attachment;filename=$file_name.xls “);
header(“Content-Transfer-Encoding: binary “);

xlsBOF();

// Top of the excel sheet to write header
xlsWriteLabel(0,0,”User Data – CSV”);

// Make column labels. (at line 3)
xlsWriteLabel(2,0,”Id”);
xlsWriteLabel(2,1,”Name”);
xlsWriteLabel(2,2,”Phone”);
xlsWriteLabel(2,3,”Address”);

// To start writing from row three from top
$xlsRow = 3;

// Put data records from mysql by while loop.
while($row=mysql_fetch_array($res)){

xlsWriteNumber($xlsRow,0,$row['id']); // 0 indicates column number
xlsWriteLabel($xlsRow,1,$row['name']);
xlsWriteLabel($xlsRow,2,$row['phone']);
xlsWriteLabel($xlsRow,3,$row['address']);

$xlsRow++;
}
xlsEOF();
exit();
?>

This script is tested and working well. But if got any questions, use comment form for the feedback.

Arif

Popularity: 7%