Linux commands with mysql

LinuxMoz

Linux Stuff && Coffee

MySQL Commands With Examples

The following MySQL Commands were originally split into several smaller blog posts that I had built up over the years, I have now consolidated the articles into a single post (feel free to link to this resource from your site).

Please note this article contains commands & examples for the mysql command line client, it does not contain information for phpMyadmin or similar GUI based software.

MySQL Set Root Password

By default MySQL has no password set, this might be fine for a private development environment but unacceptable for production servers. You can set the mysql root password various ways but below is a nice simple method that works:

Set / Change MySQL Users Passwords from the Linux Shell

You should now be able to restart MySQL and login with your new root password.

How To Connect to MySQL

To connect to your local MySQL server from the command line enter:

If you need to login to a remote MySQL server, you cn either SSH to the server and login or use the following commnd (if the server allows external connections):

MySQL Create Database

The following command will create a new MySQL database:

Backup a MySQL Database using mysqldump

Backing up a MySQL database to a flat file is refered to as “dumping the database”, there are several ways to acomplish this taske here are a few of the methods I use.

Basic mysqldump to a .sql file:

You can also dump the database and compress on the fly by piping it through gzip:

Mysqldump a remote database & transfer over SSH using gzip compression

Note you should execute the following command on the remote server that is currently serving the database, so you are affectivly pushing the db to your local machine.

Mysqldump a remote mysql database to your local machine using SSH & gzip compression (a fast way of taking a backup of a remote database). :

Dump all MySQL Databases on a server

If you wish to dump all databses on a server to a single dump file enter:

Mysqldump & Skip Table(s)

While carrying out a nasty phpBB migration I was faced with the task of dumping a MyISAM databse with some broken tables, you will get an error “mysqldump: Error 1194” or something similar to:

The best option you have if you need to take a backup in it’s current state is to tell mysqldump to skip the tables with:

If you need to skip more than one table you can just add multiple, example below:

Once you have a backup I would recommend repairing the tables.

Dump a specific table from a mysql database

Import a MySQL Database

Simple mysql db import from a .sql file:

Import a mysql database from .sql.gz

Import a .sql file from the mysql command line (you can se the output on the console as it imports, handy if your getting an import error from mysql), first select the mysql database you wish to import into and run:

Select a Database in MySQL

How to select a database in mysql:

Show Tabels in a Database

First select the databse you wish to use and run the following to show tables in a mysql databse:

Create MySQL User

The following example creates a MySQL user called “jesus” with the password “jedimaster”:

Next you need to grant the user permission to access your database:

The above will allow permission for the user “jesus” on the database “heaven_db”.

If you want “jesus” to have access to all databases on the server you would enter:

Show MySQL Database Size

The simple way is to use the filesystem to show the mysql database size on the disk with:

If you need to find out the size of the mysql database from within mysql you could use:

List MySQL Databases

The following will list all mysql databases on a server:

This will give you an output similar to:

Drop A MySQL Database (deletes a db)

The following will drop a databases, when you drop a database you are deleting it. Be careful with this command…

Drop a MySQL Table

The following will delete (drop) a mysql table, you need to select the database you wish to use first.

How To Reset the MySQL root password

The following proccess will allow you to reset the mysql root password:

Start mysql in safe mode:

Set the mysql root password:

Restart the mysql service and you can login with your new password:

Create a MySQL table

Here is the basic create table syntax for mysql:

Here is a more complex example:

Create an INNODB Table in MySQL

The following will create an innodb table:

Convert MyISAM to INNODB

It goes without saying, backup up your db first before running such a task, but here is the mysql syntax to convert a MyISAM table to INNODB:

Repair Broken Table(s) in MySQL

If you have a a corrupt / broken table (pretty common with MyiSAM) then take a dump (see the skip broken table with mysqldump instructions above) and then run:

Источник

20 MySQL (Mysqladmin) Commands for Database Administration in Linux

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

20 mysqladmin commands examples

If you don’t have MySQL server installed or you are using older version of MySQL server, then we recommend you all to install or update your version by following our below article.

1. How to set MySQL Root password?

If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.

2. How to Change MySQL Root password?

If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.

3. How to check MySQL Server is running?

To find out whether MySQL server is up and running, use the following command.

4. How to Check which MySQL version I am running?

The following command shows MySQL version along with the current running status .

5. How to Find out current Status of MySQL server?

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.

6. How to check status of all MySQL Server Variable’s and value’s?

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

8. How to check all the running Process of MySQL server?

The following command will display all the running process of MySQL database queries.

9. How to create a Database in MySQL server?

To create a new database in MySQL server, use the command as shown below.

10. How to drop a Database in MySQL server?

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘.

11. How to reload/refresh MySQL Privileges?

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.

12. How to shutdown MySQL server Safely?

To shutdown MySQL server safely, type the following command.

You can also use the following commands to start/stop MySQL server.

13. Some useful MySQL Flush commands

Following are some useful flush commands with their description.

1. flush-hosts: Flush all host information from host cache.
2. flush-tables: Flush all tables.
3. flush-threads: Flush all threads cache.
4. flush-logs: Flush all information logs.
5. flush-privileges: Reload the grant tables (same as reload).
6. flush-status: Clear status variables.

14. How to kill Sleeping MySQL Client Process?

Use the following command to identify sleeping MySQL client process.

Now, run the following command with kill and process ID as shown below.

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

15. How to run multiple mysqladmin commands together?

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

16. How to Connect remote mysql server

To connect remote MySQL server, use the -h (host) with IP Address of remote machine.

17. How to execute command on remote MySQL server

Let’s say you would like to see the status of remote MySQL server, then the command would be.

18. How to start/stop MySQL replication on a slave server?

To start/stop MySQL replication on salve server, use the following commands.

19. How to store MySQL server Debug Information to logs?

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.

20. How to view mysqladmin options and usage

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.

We have tried our best to include almost all of ‘mysqladmin‘ commands with their examples in this article, If still, we’ve missed anything, please do let us know via comments and don’t forget to share with your friends.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.

Источник

MySQL Commands Tutorial

Connect to MySQL

If you have already installed MySQL on your Ubuntu machine, you can easily connect to the MySQL shell by issuing the following command in your Linux terminal.

In the above command:

• -u is the user
• root is the MySQL username
• -p is the password

After running the above command, you will be asked to enter the password. Once you have entered the root user password, you will be logged in to the MySQL shell.

If you have recently installed MySQL, then you will not be asked for the password.

Set or Change Password

To set or change the password for any MySQL database user, issue the following command:

Replace the user_name and new_password with your username and desired password. For example:

View Databases

In MySQL, you can view the list of databases under the user’s granted privileges by running the simple command stated below:

The above command will list all the databases.

Create a Database

You can create a database by issuing the following command in the MySQL shell:

The above statement will not create a database if there already exists a database with the name that has been provided. For example:

As you can see in the screenshot, the db1 database has been created.

Select a Database

You can use any of the listed databases by running the following command in the MySQL shell:

Create a Table

A table can be created by using the CRETE TABLE command and defining the column names and their types in the parentheses, as follows:

In this creation query:

• tb1 is the name of the table
• col1, col2 are the names of the columns in the tb1 table
• INT and VARCHAR are the datatypes of the specified columns
• col1 is defined as the primary key

View Tables

To list all the tables in the present database, run the simple SHOW TABLES command in the MySQL shell.

View Table Structure

The structure of the table, including column names, their types, and their default values, can be seen by running the DESC command.

Insert Data

Data can be inserted into the table by providing the column names into which you want to insert data, as well as the data or values, using the following syntax:

View Table Data

The SELECT statement is used to view the data in a table. You can either provide the asterisk symbol (*) to select all the columns, or you can provide the specific columns that you want to view.

Delete Data from Table

To delete a row from a table, provide the table name to the DELETE FROM command, along with the WHERE conditional clause.

Delete a Table

Deleting a table is as easy as saying it. You can delete any table in the present database by running the statement given below.

Delete a Database

Deleting or dropping a database is the same as deleting a table in MySQL. Simply use the DROP DATABASE command with the database name.

Источник