MYSQL Administration

mysqladmin — Client for Administering a MySQL Server

2009-07-11T21:11:00.000-07:00

mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

mysqladmin supports the commands described in the following list. Some of the commands take an argument following the command name.

* create db_name

Create a new database named db_name.
* debug
Tell the server to write debug information to the error log.
* drop db_name

Delete the database named db_name and all its tables.
* extended-status
Display the server status variables and their values.

Mysqladmin

2009-07-11T21:09:00.000-07:00

mysqladmin

The mysqladmin program is used to administrate various aspects of the MySQL database server. Using it, the administrator can perform tasks such as: create and delete databases, shutdown the database server, update the privilege tables, and view running MySQL processes.

The general syntax is:
%>mysqladmin [options] command(s)

Where [options] can be one or a series of options used in conjunction with the mysqladmin program, and [database] is the name of the database to use. Since it is assumed to be the reader's first time using the MySQL monitor, take a moment to review all offered options by executing the following command:

%>mysqladmin --help

This produces a long list of options that can be used in conjunction with the mysqladmin program. As a demonstration of how these options are used, let's use mysqladmin to create a new database named widgets, which will be used throughout the remainder of this article to demonstrate various other useful MySQL functions. A new database is created as follows:

%>mysqladmin -u root -p create widgets
Enter Password:

Upon execution, mysqladmin will create the database and return to the shell prompt. Typically, the next step is to secure the new database by modifying the privilege tables. Details regarding how this is accomplished is the subject of the next section.

Selecting a database

2009-07-11T21:06:00.000-07:00

Of course, simply connecting to the MySQL server isn't going to accomplish much. Chances are you will want to select a database to work with.

This is accomplished in one of two ways:
Including the name of the database along with the mysql connection command.

For example, the command used to both connect to the MySQL server and select the mysql database is:

%>mysql -u root -p mysql

This might be misleading for some readers, as it seems as if the intent is to input mysql as the password. This is not correct. Take a moment to review the syntax as described in the mysql --help output, and it will be apparent that �u root �p actually comprise the [options] component of the syntax, and mysql comprises the [database] component.

Once connected, select the database using the use command, as follows: mysql>use mydatabase
Once executed, all queries not explicitly specifying a database name will be directed towards the hypothetical mydatabase database.

mysqlhotcopy - database backups mysql

2009-07-11T21:01:00.000-07:00

mysqlhotcopy
The mysqlhotcopy utility is a perl script that uses several basic system and SQL commands to backup a database. More specifically, it will lock the tables, flush the tables, make a copy, and unlock the tables. Although it is the fastest method available for backing up a MySQL database, it is limited to backing up only those databases residing on the same machine as where it is executed.
The function mysqlhotcopy can be executed to backup one database, a number of databases, or only those databases matching a name specified by a regular expression. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqlhotcopy to backup just one database:
%>mysqlhotcopy [options] db_name /path/to/new_directory

Using mysqlhotcopy to backup just several databases:
%>mysqlhotcopy [options] db_name_1 ... db_name_n /path/to/new_directory

Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:
%>mysqlhotcopy [options] db_name./regex/

The options can be viewed by executing the following command:
%>mysqlhotcopy --help

Examples:Experiment with mysqlhotcopy by backing up the widgets database to the directory path "/usr/mysql/backups/". Execute the following command:

%>mysqlhotcopy -u root -p widgets /usr/mysql/backups
As a second example, assume that the widgets database contains the tables: "products2000", "products2001", "clientele2000", and "clientele2001", with the four digits at the end of each name representing the year for which that data represents. The administrator wants to backup only those tables relative to the year "2000":
%>mysqlhotcopy -u root -p widgets./^.+('2000')$/ /usr/mysql/backups

In the above example, the regular expression /^.+('2000')$/ tells mysqlhotcopy to only backup those tables ending with the string "2000".

mysqldump - database backups mysql

2009-07-11T20:56:00.000-07:00

mysqldump
The utility mysqldump provides a rather convenient way to dump existing data and table structures. Note that while mysqldump is not the most efficient method for creating backups (mysqlhotcopy is, described next), it does offer a convenient method for copying data and table structures which could then be used to repopulate another SQL server, that server not even necessarily being MySQL.
The function mysqldump can be used to backup all databases, several databases, one database, or just certain tables within a given database. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqldump to backup just one database:

%>mysqldump [options] db_name

Using mysqldump to backup several tables within a database:

%>mysqldump [options] db_name table1 table2 . . . tableN

Using mysqldump to backup several databases:

%>mysqldump [options] --databases [options] db_name1 db_name2 . . . db_nameN

Using mysqldump to backup all databases: %>mysqldump [options] --all-databases [options]
The options can be viewed by executing the following command: %>mysqldump --help

Examples:
Backing up both the structure and data found within the widgets database would be accomplished as follows:

%>mysqldump -u root -p --opt widgets
Alternatively, perhaps just a backup of the data is required. This is accomplished by including the option --no-create-info, which means no table creation data: %>mysqldump -u root -p --no-create-info widgets
Another variation is just to backup the table structure. This is accompished by including the option --no-data, which means no table data:

%>mysqldump -u root -p --no-data widgets

If you are planning on using mysqldump for reason of backing up data so it can be moved to another MySQL server, it is recommended that you use the option '--opt'. This will give you an optimized dump which will result in a faster read time when you later load it to another MySQL server.
While mysqldump provides a convenient method for backing up data, there is a second method which is both faster and more efficient. It is described in the next section.

MYSQL STOP START

2009-07-11T04:28:00.000-07:00

* To start mysql server:# /etc/init.d/mysql start
* To stop mysql server:# /etc/init.d/mysql stop
* To restart mysql server# /etc/init.d/mysql restart

# To Start MySQL Server/sbin/service mysqld start
# To Stop MySQL Server/sbin/service mysqld stop
# To Restart MySQL Server/sbin/service mysqld restart

Login to Mysql commands

2009-07-11T04:27:00.001-07:00

MYSQL ADMINISTRATION

2009-06-22T11:38:00.001-07:00

Learn MYSQL Administration