Finding help content for MySQL databases administration.
Run ‘help contents’ for a list of all accessible topics
mysql>
help contents
You asked for help about help category: “Contents” For more information, type ‘help [item]‘, where [item] is one of the following categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Table Maintenance
Transactions
User-Defined Functions
Utility
To go to the individual catefories run the following example command.
mysql>
help Account Management
You asked for help about help category: “Account Management” For more information, type ‘help [item]‘, where [item] is one of the following topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
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.
