Difference between revisions of "MySQL"
(→Editing Databases and Tables) |
Chantal21I (Talk | contribs) |
||
(14 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
− | = MySQL Setup = | + | == MySQL Setup == |
In order to run MySQL you will need a MySQL server to work with - you can establish a server on one of your own computers, or use a webserver with MySQL installed. | In order to run MySQL you will need a MySQL server to work with - you can establish a server on one of your own computers, or use a webserver with MySQL installed. | ||
Line 5: | Line 5: | ||
=== Installing MySQL === | === Installing MySQL === | ||
− | MySQL is widely supported on | + | MySQL is widely supported on Windows, Linux and Mac OSX. MySQL can be downloaded from [http://www.mysql.com the official website] or (for Linux systems) through a package manager. For Windows installations both a GUI and a command-line utility are available, which you may use if you plan on following the instructions in this guide verbatim. |
− | + | === Setup on a personal computer === | |
− | + | ||
− | == Setup on a personal computer == | + | |
Once MySQL is installed on your computer and the MySQL daemon is running (in Arch, /etc/rc.d/mysqld start), the next step is to establish users. If you defined a root MySQL password on setup, you can use this to establish a new user; otherwise, just hit enter at any [[password]] prompts you encounter. | Once MySQL is installed on your computer and the MySQL daemon is running (in Arch, /etc/rc.d/mysqld start), the next step is to establish users. If you defined a root MySQL password on setup, you can use this to establish a new user; otherwise, just hit enter at any [[password]] prompts you encounter. | ||
Line 22: | Line 20: | ||
|} | |} | ||
− | This will log you into | + | This will log you into MySQL as root after a password prompt. At this point, you can establish your other users with the SQL CREATE USER |
query. For example, if you wanted to create a user, "K_Mitnick": | query. For example, if you wanted to create a user, "K_Mitnick": | ||
Line 36: | Line 34: | ||
This user will be created with absolutely no privileges: they can log into your server but do little else. | This user will be created with absolutely no privileges: they can log into your server but do little else. | ||
− | === Setting Permissions === | + | ==== Setting Permissions ==== |
In order to allow your users to execute queries and interact with your databases, you must indicate to the server just what they are allowed to do. The most simple form of this is | In order to allow your users to execute queries and interact with your databases, you must indicate to the server just what they are allowed to do. The most simple form of this is | ||
Line 71: | Line 69: | ||
You can now log on as a user other than root. | You can now log on as a user other than root. | ||
− | == Basic Database Operation == | + | === Basic Database Operation === |
− | === Display === | + | ==== Display ==== |
From the main MySQL prompt you see when you first log in, you usually are not logged into a particular database. In order to see all of the databases in your server, type: | From the main MySQL prompt you see when you first log in, you usually are not logged into a particular database. In order to see all of the databases in your server, type: | ||
Line 86: | Line 84: | ||
This will display all of the databases on a server. | This will display all of the databases on a server. | ||
− | There are two ways to reference a table. One is by using the format | + | There are two ways to reference a table. One is by using the format (database name).(table name) to refer to its full path. The other is to set the database you're working in as your currently active database with the USE command: |
{| class="wikitable" | {| class="wikitable" | ||
Line 95: | Line 93: | ||
|} | |} | ||
− | From this point onwards, you can simply refer to a table by name. | + | From this point onwards, you can simply refer to a table by name. Furthermore, you can see a list of all tables in the selected database with |
{| class="wikitable" | {| class="wikitable" | ||
Line 104: | Line 102: | ||
|} | |} | ||
− | === Creating and Deleting Databases and Tables === | + | ==== Creating and Deleting Databases ==== |
+ | |||
+ | The procedure for the creation and deletion of entire databases is relatively simple. | ||
+ | |||
+ | To create: | ||
+ | |||
+ | {| class="wikitable" | ||
+ | | | ||
+ | <syntaxhighlight lang="SQL"> | ||
+ | CREATE DATABASE <name>; | ||
+ | </syntaxhighlight> | ||
+ | |} | ||
+ | |||
+ | To delete: | ||
+ | |||
+ | {| class="wikitable" | ||
+ | | | ||
+ | <syntaxhighlight lang="SQL"> | ||
+ | DELETE DATABASE <name>; | ||
+ | </syntaxhighlight> | ||
+ | |} | ||
+ | |||
+ | ==== Creating and Deleting Tables ==== | ||
In order to create a new table, we use the CREATE query. The syntax for this query is | In order to create a new table, we use the CREATE query. The syntax for this query is | ||
Line 133: | Line 153: | ||
To delete a table, simply use the DROP TABLE <table> query. | To delete a table, simply use the DROP TABLE <table> query. | ||
− | === Editing | + | ==== Editing Tables ==== |
Now that you have a table, you can begin to put values into it. There are three methods by which you can manipulate the data in a table: one is the INSERT query, another is the UPDATE query, and the last in the DELETE query. | Now that you have a table, you can begin to put values into it. There are three methods by which you can manipulate the data in a table: one is the INSERT query, another is the UPDATE query, and the last in the DELETE query. | ||
Line 201: | Line 221: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|} | |} | ||
+ | |||
+ | == MySQL Commands == | ||
+ | |||
+ | *Full list here: http://www.pantz.org/software/mysql/mysqlcommands.html | ||
+ | |||
+ | === Create a backup of a database === | ||
+ | |||
+ | <syntaxhighlight lang="bash">mysqldump '''<database>''' > '''<database>'''.sql</syntaxhighlight> | ||
+ | |||
+ | === Restore an entire database === | ||
+ | |||
+ | <syntaxhighlight lang="bash">mysqldump '''<database>''' > '''<database>'''.backup.sql # Always dump the old databse if it is still there, in case we need to bring it back. | ||
+ | mysql '''<database>''' < '''<sql-file>'''.sql</syntaxhighlight> | ||
+ | |||
+ | === Restore a single table === | ||
+ | |||
+ | <syntaxhighlight lang="bash">mysqldump '''<database>''' > '''<database>'''.backup.sql # Backup database, just in case. | ||
+ | cat '''<database-backup>'''.sql | sed -n '/-- Table structure for table `'''<table_name>'''`/,/-- Table/{ /^--.*$/d;p }' > '''<table_name>'''_table.sql | ||
+ | mysql '''<database>''' < '''<table_name>'''_table.sql</syntaxhighlight> | ||
+ | |||
+ | === Dump mysql user privileges === | ||
+ | |||
+ | <syntaxhighlight lang="bash">mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'</syntaxhighlight> | ||
+ | |||
+ | === Restore a single database from a full dump === | ||
+ | |||
+ | <syntaxhighlight lang="bash">mysql --one-database database_name < fulldump.sql</syntaxhighlight> | ||
+ | |||
+ | === Extract sql for a single database from a full dump === | ||
+ | |||
+ | <syntaxhighlight lang="bash">sed -n '/^-- Current Database: `singledb`/,/^-- Current Database: `/p' fulldump.sql > singledb.sql</syntaxhighlight> | ||
+ | |||
+ | === Dumping a database in Plesk === | ||
+ | |||
+ | <syntaxhighlight lang="bash">mysqldump -u admin -p database > .sql</syntaxhighlight> | ||
+ | |||
+ | |||
+ | For information on troubleshooting MySQL issues, check out the [[MySQL Troubleshooting]] section. | ||
+ | |||
+ | {{Administration}} | ||
+ | syntaxhighlight lang="SQL"> | ||
+ | DELETE FROM user WHERE username="Selketraz"; |
Latest revision as of 04:54, 17 November 2012
Contents
MySQL Setup
In order to run MySQL you will need a MySQL server to work with - you can establish a server on one of your own computers, or use a webserver with MySQL installed.
Installing MySQL
MySQL is widely supported on Windows, Linux and Mac OSX. MySQL can be downloaded from the official website or (for Linux systems) through a package manager. For Windows installations both a GUI and a command-line utility are available, which you may use if you plan on following the instructions in this guide verbatim.
Setup on a personal computer
Once MySQL is installed on your computer and the MySQL daemon is running (in Arch, /etc/rc.d/mysqld start), the next step is to establish users. If you defined a root MySQL password on setup, you can use this to establish a new user; otherwise, just hit enter at any password prompts you encounter.
In order to set up your MySQL databases, you'll first need to log into MySQL - at this point the only MySQL user will be your root user, so log in with:
<syntaxhighlight lang="bash"> mysql -p -u root </syntaxhighlight> |
This will log you into MySQL as root after a password prompt. At this point, you can establish your other users with the SQL CREATE USER query. For example, if you wanted to create a user, "K_Mitnick":
<syntaxhighlight lang="SQL"> CREATE USER K_Mitnick; </syntaxhighlight> |
Note the terminating semicolon - this indicates to MySQl that you wish to send your input as a query. Another method of doing this is by terminating your queries with '\g' - there is no difference between the two, it is simply a matter of personal preference.
This user will be created with absolutely no privileges: they can log into your server but do little else.
Setting Permissions
In order to allow your users to execute queries and interact with your databases, you must indicate to the server just what they are allowed to do. The most simple form of this is
<syntaxhighlight lang="SQL"> GRANT ALL ON <database name> TO <username> IDENTIFIED BY '<password>'; </syntaxhighlight> |
This kind of heavy handed allowance gives the user total freedom to execute any SQL query that they wish. IT SHOULD NEVER be given to the normal user, as this will create a serious vulnerability in your database. |
A more reasonable form of this would be
<syntaxhighlight lang="SQL"> GRANT SELECT ON <database name> TO <username> IDENTIFIED BY '<password>'; </syntaxhighlight> |
This only gives access to the SELECT query for the user, which essentially makes their access read-only. The GRANT query can be used with any SQL query as a parameter in order to grant a user the ability to use that query.
After you have changed permissions, it is a good idea to ensure that MySQL is up-to-date with user permissions with
<syntaxhighlight lang="SQL"> FLUSH PRIVILEGES; </syntaxhighlight> |
You can now log on as a user other than root.
Basic Database Operation
Display
From the main MySQL prompt you see when you first log in, you usually are not logged into a particular database. In order to see all of the databases in your server, type:
<syntaxhighlight lang="SQL"> SHOW DATABASES; </syntaxhighlight> |
This will display all of the databases on a server.
There are two ways to reference a table. One is by using the format (database name).(table name) to refer to its full path. The other is to set the database you're working in as your currently active database with the USE command:
<syntaxhighlight lang="SQL"> USE <database>; </syntaxhighlight> |
From this point onwards, you can simply refer to a table by name. Furthermore, you can see a list of all tables in the selected database with
<syntaxhighlight lang="SQL"> SHOW TABLES; </syntaxhighlight> |
Creating and Deleting Databases
The procedure for the creation and deletion of entire databases is relatively simple.
To create:
<syntaxhighlight lang="SQL"> CREATE DATABASE <name>; </syntaxhighlight> |
To delete:
<syntaxhighlight lang="SQL"> DELETE DATABASE <name>; </syntaxhighlight> |
Creating and Deleting Tables
In order to create a new table, we use the CREATE query. The syntax for this query is
<syntaxhighlight lang="SQL"> CREATE TABLEFor example, if we were to create a table called 'user' containing usernames and hashed passwords, we might want it to have 3 sections - first of all, a number to reference the rows by, secondly a plaintext username, and finally, a hashed password. In order to generate the ID, we would use the special PRIMARY KEY feature of SQL - each table should have a primary key, which automatically updates itself for each entry and is used to refer to elements in a table. The username and password hash would be VARCHARs, which is an SQL datatype that can hold up to 65,535 characters per row. So for this table, our declaration would be as follows:
There are a great many SQL datatypes to be used to format the contents of a column. For a full list of datatypes, check The MySQL Website. To delete a table, simply use the DROP TABLE
|