Exporting and Importing mysql databases
Exporting the Database.
In this example, we will export the ‘mydb’ database into a file called ‘mydb.sql’:
mysqldump -u root -p mydb > mydb.sql
Importing the data is just as easy but involves two steps.
The first step is to create a blank database ready to receive the data.
mysqladmin -u root -p create mydb1
Once done, all that is left is to actually import the data:
mysql -u root -p mydb1 < mydb.sql
Creating mysql user and then setting password for user.
mysql> create user @localhost;
mysql> SET PASSWORD FOR r@localhost=PASSWORD(”);
How to set up password for mysql root user.
mysql> SET PASSWORD FOR root@localhost=PASSWORD(’);
In the below eaxmple, database and user is the same - serverbuddies
mysql> create database serverbuddies;
How to give permission to user - serverbuddies.
mysql> grant INSERT,SELECT on root.* to serverbuddies@localhost;
mysql> grant CREATE,INSERT,SELECT,DELETE,UPDATE on serverbuddies.* to serverbuddies@localhost;
mysql> grant CREATE,INSERT,SELECT,DELETE,UPDATE on serverbuddies.* to serverbuddies;
The next example has user as admin and databses as ubuntu.
mysql> create database ubuntu;
Setting permission to user admin for the database ubuntu.
mysql> grant CREATE,INSERT,SELECT,DELETE,UPDATE on ubuntu.* to admin@localhost;
mysql> grant CREATE,INSERT,SELECT,DELETE,UPDATE on ubuntu.* to archive;
mysql> exit
How to take mysql remote and local backup.
Doing Remote Mysql databse Backup:
`which mysqldump` -h -uusername -ppassword –opt database > /filename.sql
Local Host mysql Backup:
`which mysqldump` -uroot -ppassword –opt database > /filename.sql
rsync to backup your home directory and mysql databases.
Run the below rsync command using “nohup”, nohup runs a command immune to hangups, with output to a non-tty.
Rsync your home directory’s to your backup server.
Replace $IP with the IP address for your production server.
Run the below commands from your backup server.
#rsync -vrplogDtH –exclude=virtfs/ –progress -e ssh root@$IP:/home/ /home/
rsync all mysql databases.
#rsync -vrplogDtH –progress -e ssh root@$IP:/var/lib/mysql/ /var/lib/mysql/
Backup DB server_support
rsync -vrplogDtH –progress -e ssh root@$IP:/var/lib/mysql/server_support /var/lib/mysql/
In Usermin’s MySQL Database module, how can I restrict the databases that each user can see and use?
By default the module will list all of the databases on your system on the main page, even if some are not actually usable by the logged-in user. To change this, follow these steps :
Login to Webmin on the same server, and enter the Usermin Configuration module.
Click on Usermin Module Configuration.
Click on MySQL Database in the list.
In the Database access control list field, remove the existing *: * line and enter one line per user, containing the username, a colon and
list of databases he is allowed to use. For example, you could enter :
jcameron: database1
fred: database2 database3
joe: *
A * in the database column means all databases, while a * in the username column means any user not listed so far.
Hit the Save button to activate the restrictions.
MySQL service is failing to start up with error “Can’t init databases”
Following is the error message in mysql server log file /var/log/mysqld.log:
/usr/libexec/mysqld: Can’t create/write to file ‘/tmp/ibCfJwf1? (Errcode: 13)
While trying to start it, following is the error.
Initializing MySQL database: [ OK ]
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
This error occurs when MySQL is not able to access the /tmp directory to write and create temporary files. Make sure /tmp is owned by root and 1777 permisssion is set on /tmp directory.
Following commands will fix the problem.
# chown root:root /tmp
# chmod 777 /tmp
# /etc/init.d/mysqld start
Now MySQL should start without a problem.
