Difference between revisions of "Mysql"
From Linuxintro
imported>ThorstenStaerk |
imported>ThorstenStaerk |
||
| (10 intermediate revisions by the same user not shown) | |||
| Line 58: | Line 58: | ||
+---------------------------+ | +---------------------------+ | ||
17 rows in set (0.00 sec) | 17 rows in set (0.00 sec) | ||
| + | |||
| + | mysql> desc time_zone; | ||
| + | +------------------+------------------+------+-----+---------+----------------+ | ||
| + | | Field | Type | Null | Key | Default | Extra | | ||
| + | +------------------+------------------+------+-----+---------+----------------+ | ||
| + | | Time_zone_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | ||
| + | | Use_leap_seconds | enum('Y','N') | NO | | N | | | ||
| + | +------------------+------------------+------+-----+---------+----------------+ | ||
| + | 2 rows in set (0.00 sec) | ||
mysql> select * from time_zone; | mysql> select * from time_zone; | ||
| Line 64: | Line 73: | ||
mysql> | mysql> | ||
</pre> | </pre> | ||
| + | |||
| + | == backup == | ||
| + | To create a backup of your database ''database1'' as user ''wikiuser'' stored to the file ''dump.sql'' use the command | ||
| + | mysqldump ''database1'' -u ''wikiuser'' -p > ''dump.sql'' | ||
| + | It will ask you for the password and begin the backup aka export aka dump. | ||
| + | |||
| + | === for all databases === | ||
| + | mysqldump --all-databases --user=root -p | gzip > all_databases.sql.gz | ||
| + | |||
| + | restore: | ||
| + | mysql <database> -h <host> -u <user> -p < dump.sql | ||
| + | |||
| + | == show users == | ||
| + | select User from mysql.user; | ||
| + | |||
| + | == set password == | ||
| + | Here is how to set a password for a mysql user, in this example [[mythtv]]: | ||
| + | set password for 'mythtv'@'localhost' = PASSWORD ('mythtv'); | ||
| + | |||
| + | = See also = | ||
| + | * [[migrating a database from mysql to sqlite]] | ||
| + | * [http://www.manpagez.com/man/1/mysql/ man page of the command mysql, the command to connect to the database mysql] | ||
Latest revision as of 09:10, 22 April 2014
MySQL is a free database. This article describes how to install it, start it and how to connect to it. It has been tested with SUSE Linux 11.1 but should work with any Linux.
Contents
How to install mysql
sudo /sbin/yast -i mysql
How to start the database
sudo /etc/init.d/mysql start
How to connect to mysql
mysql
How to work with mysql
Here is a short example how to work with mysql, including an SQL statement:
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67 SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
mysql> desc time_zone;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| Time_zone_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Use_leap_seconds | enum('Y','N') | NO | | N | |
+------------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from time_zone;
Empty set (0.00 sec)
mysql>
backup
To create a backup of your database database1 as user wikiuser stored to the file dump.sql use the command
mysqldump database1 -u wikiuser -p > dump.sql
It will ask you for the password and begin the backup aka export aka dump.
for all databases
mysqldump --all-databases --user=root -p | gzip > all_databases.sql.gz
restore:
mysql <database> -h <host> -u <user> -p < dump.sql
show users
select User from mysql.user;
set password
Here is how to set a password for a mysql user, in this example mythtv:
set password for 'mythtv'@'localhost' = PASSWORD ('mythtv');
