MySQL/Guía de inicio
Esta guía describe instrucciones básicas para el uso y configuración de MySQL en Linux.
Introducción a MySQL
Antecedentes
MySQL es un popular servidor de bases de datos usado en múltiples aplicaciones. SQL traduce (S)tructured (Q)uery (L)anguage: Lenguaje de Consultas eStructuradas, MySQL hace uso de este para comunicarse con otros programas. Sobre aquel lenguaje MySQL tiene sus propias funciones SQL expandidas para proporcionar funcionalidades adicionales a los usuarios. En este documento, veremos cómo hacer la instalación inicial de MySQL, generar bases de datos, tablas, y crear nuevos usuarios. Emecemos con la instalación.
Instalar MySQL
En caso de requerir funcionalidades específicas de MySQL, revise los sus ajustes USE disponibles previo a emerger el paquete:
root #emerge --ask dev-db/mysqlAl finalizar la instalación, es posible que se muestre el siguiente aviso:
Posiblemente usted desee correr:
"emerge --config =dev-db/mysql-[version]"
si esta es una instalación nueva.
Ya que esta es una nueva instalación, es necesario ejecutar el comando anterior. Presione ENTER cuando se lo soliciten en la configuración de MySQL; aquella establece una base de datos MySQL que contiene información administrativa en la que se encuentran bases de datos, tablas, usuarios, permisos entre otros. La configuración sugiere cambiar la clave root lo mas pronto posible.
root #emerge --config =dev-db/mysql-[version]* MySQL DATADIR is /var/lib/mysql * Press ENTER to create the mysql database and set proper * permissions on it, or Control-C to abort now... Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, issue the following commands to start the server and change the applicable passwords: /etc/init.d/mysql start /usr/bin/mysqladmin -u root -h pegasos password 'new-password' /usr/bin/mysqladmin -u root password 'new-password' Depending on your configuration, a -p option may be needed in the last command. See the manual for more details.
Si el comando previo aborta debido a que el nombre del anfitrion "hostname" es localhost, entonces cambielo por otro, por ejemplo gentoo. Actualize el archivo /etc/conf.d/hostname y reinicie el servicio /etc/init.d/hostname.
Alguna información de MySQL no relacionada a la ebuild ha sido omitida para mantener este documento lo mas consistente posible.
Desde mysql-4.0.24-r2, las contraseñas son ingresadas durante la fase de configuración, haciendo mas segura la instauración de la contraseña de administrador.
El script de configuración ya ha impreso los comandos necesarios para establecer la contraseña.
Si usa OpenRC, realice el siguiente comando:
root #rc-service mysql start* Re-caching dependency info (mtimes differ)... * Starting mysqld (/etc/mysql/my.cnf) ... [ ok ]
Si usa Systemd, emita el siguiente comando:
root #systemctl restart mysqld.serviceCon >=dev-db/mariadb-10.1.18, use:
root #systemctl restart mariadb.serviceLuego establezca la contraseña root:
root #/usr/bin/mysqladmin -u root -h localhost password 'new-password'Posteriormente verifique que la contraseña root ha sido establecida exitosamente tratando de entrar al servidor MySQL:
user $mysql -u root -h localhost -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
El conmutador -u establece el usuario con el que se ingresará. El conmutador -h establece el anfitrión; usualmente este será localhost a menos que este configurando un servidor remoto. Finalmente, -p le indica al cliente mysql que usted ingresará una contraseña para acceder a la base de datos. Note el indicador mysql> en la terminal, acá es en donde se ingresan todos sus comandos. Ahora que esta en la terminal de mysql como el usuario root, puede empezar a configurar la base de datos.
La instalación por defecto de mysql es destinada a sistemas de desarollo. Quizas usted desee ejecutar /usr/bin/mysql_secure_installation para establecer configuraciones de seguridad
Configurar la base de datos
Crear la base de datos
Hemos ingresado y se muestra la terminal MySQL. Primero hagamos una revision de las bases de datos que actualmente se tienen. Para ello se usa el comando SHOW DATABASES.
mysql>SHOW DATABASES;+----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.09 sec)
Por favor recuerde que los comandos MySQL deben terminar con un puntoycoma --
;A pesar de que ya existe una base de datos de prueba creada, vamos a hacer la nuestra. Aquellas son creadas usando el comando CREATE DATABASE. Vamos a fundar una llamada "gentoo".
mysql>CREATE DATABASE gentoo;Query OK, 1 row affected (0.08 sec)
La salida nos permite saber que el comando fué ejecutado sin ningún error. En este caso, la fila 1 fué modificada. Esta es una referencia a la base de datos MySQL principal, que lleva una lista de todas las bases de datos. No necesita preocuparse por los detalles de fondo. El último número es el tiempo de ejecución de la petición. Puede ferificar que la base de datos fue creada ejecutando el comando SHOW DATABASES de nuevo.
mysql>SHOW DATABASES;+----------+ | Database | +----------+ | gentoo | | mysql | | test | +----------+ 3 rows in set (0.00 sec)
La base de datos ha sido efectivamente creada. En orden de crear las tablas dentro de nuestra nueva base de datos Gentoo, necesitamos seleccionarla como nuestra base de datos actual. Para lograrlo, usamos el comando USE. En este comando se ingresa el nombre de la base de datos que se quiere usar como su base de datos actual. Otra opción es invocarlo en la linea de comandos luego del interruptor -D. Continuemos con el proceso cambiando a la base de datos Gentoo.
mysql>USE gentoo;Database changed
Y la base de datos actual ahora es la creada anteriormente como gentoo. Ya que estamos en ella, podemos empezar a crear algunas tablas y poner información en ellas.
Trabajando con tablas en MySQL
Crear una tabla
En la estructura de MySQL, hay bases de datos, tablas, registros y campos. Las bases de datos contienen tablas, las tablas contienen registros, los registros contienen campos, los cuales de hecho almacenan información. Esta estructura posibilita al usuario seleccionar como desea acceder su información. Ya que introducimos las bases de datos, analicemos las tablas. Primero, las tablas pueden ser listadas de manera similar a las bases de datos usando el comando SHOW TABLES. En este momento no hay tablas en nuestra base de datos Gentoo, como nos muestra correr el comando anterior:
mysql>SHOW TABLES;Empty set (0.00 sec)
Esto significa que debemos crear algunas tablas. Para poder hacerlo, usamos el comando CREATE TABLE. No obstante, este comando es algo diferente que simplemente llamar CREATE DATABASE. Este requiere una lista de argumentos. De la siguiente forma:
CREATE TABLE [table_name] ([field_name] [field_data_type]([size]));
table_name es el nombre de la tabla que queremos crear. En este caso, creemos una tabla llamada developers. Esta tabla va a contener los nombres de desarrolladores, email y cargo.
field_name contiene el nombre del campo. Requerimos 3 campos en este caso: nombre, email y cargo. También un campo PRIMARY KEY es definido para relacionar otras tablas y se va a auto-incrementar con cada inserción. PRIMARY KEY es sugerida, pero no requerida ya que MySQL va a guardar esta variable internamente. Indexaciónes, de las cuales PRIMARY KEY hace parte, permite acceso mas rápido a los datos a medida que las tablas crecen.
field_data_type es el tipo de información a ser almacenada. Los diferentes formatos disponibles se encuentran en Tipos de datos MySQL. Para nuestros propósitos, vamos a usar el tipo de variable VARCHAR para todos nuestros campos de datos y INTEGER para los campos llave. VARCHAR es una de los tipos de datos mas símples en cuanto a trabajar con oraciones se refiere.
size es el tamaño de los datos que un campo individual va a almacenar. Para este caso usaremos 128. Esto implica que el campo de datos VARCHAR es de 128 bytes. Usted puede sin ningun peligro para la fecha, interpretarlo como 128 caracteres, sin embargo hay una explicación más tecnica que puede revisar en el sitio web anteriormente provisto. Ahora que sabemos cómo crear una tabla ¡hagamoslo!
mysql>CREATE TABLE developers ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));Query OK, 0 rows affected (0.11 sec)
Aparentemente nuestra tabla ha sido efectivamente creada. Comprobemoslo con el comando SHOW TABLES:
Translations:MySQL/Startup Guide/41/es
mysql>SHOW TABLES;+------------------+ | Tables_in_gentoo | +------------------+ | developers | +------------------+ 1 row in set (0.00 sec)
Sí, esta es nuestra tabla. Aunque parece no tener información alguna en los tipos de campos configurados. Para ello usamos el comando DESCRIBE (ó la contracción DESC), cuyo argumento es el nombre de la tabla. Veamos qué proporciona la tabla "developers".
mysql>DESCRIBE developers;+-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(128) | YES | | NULL | | | email | varchar(128) | YES | | NULL | | | job | varchar(128) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
Ello muestra los diferentes campos y sus tipos, además de algunos atributos extra fuera del alcance de esta guía. Para mayor información revise el Manueal de Referencia MySQL. Ahora que tenemos una tabla funcional, poblemosla.
Poblando la base de datos MySQL
Nosotros poblamos la tabla (o añadimos datos) usando el comando INSERT; Así como CREATE TABLE, este también tiene un formato específico:
Translations:MySQL/Startup Guide/47/es
INSERT INTO table (col1, col2, ...) VALUES('value1', 'value2', ...);
This command is used to insert a record into table. table contains the MySQL table we wish to enter the information into. The table name may be followed by the list of columns to insert data into and VALUES() contains the values you wish to insert into the table. You may omit the list of columns if you insert a value into each one and if you write the values in the same order the columns have been defined. For the auto-increment column, a NULL value will use the next available value. In this case, we want to insert data into the developers table. Let's insert sample records:
mysql>INSERT INTO developers VALUES(NULL, 'Joe Smith', 'joesmith@gentoo.org', 'toolchain');Query OK, 1 row affected (0.06 sec)
## (If you don't know the order of the columns in the table or want to insert an incomplete record)
mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe');
Query OK, 1 row affected (0.01 sec)
According to our return result, it appears that the record was inserted correctly. What if we want to input more information than just one record? That's where the LOAD DATA command comes into play. This loads records from a tab separated file. Let's try that by editing a file in our home directory with the records. We'll call this file records.txt . Here's a sample:
3 John Doe johndoe@gentoo.org portage
4 Chris White chriswhite@gentoo.org documentation
5 Sam Smith samsmith@gentoo.org amd64
Be sure you know what data you're dealing with. It's very unsafe to use
LOAD DATA when you are uncertain of the file's contents!Now the LOAD DATA command has a somewhat elongated definition, but we'll use the simplest form here.
LOAD DATA LOCAL INFILE '/path/to/filename' INTO TABLE table;
/path/to/filename is the directory and filename that will be used. table is the name of our table. In this case, our file is ~/records.txt and the table is developers.
mysql>LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
If you come up with any strange behavior, make sure your fields are separated by tabs. If you paste information into your infile from another source, it may convert your tabs to spaces.
That worked well. However, this simply inserts records, and does not give you any sort of control over MySQL. Many web applications use sql scripts in order to setup MySQL quickly and easily. If you want to use an sql script, you'll need to run mysql in batch mode, or source the file. Here's an example of running mysql in batch mode:
user $mysql -u root -h localhost -p < sqlfileLike LOAD DATA , be sure you can tell what sqlfile does. Failure to do so may cause your database to be compromised! Another way you can accomplish this is to use the source command. This command will run mysql commands from an sql file while in the mysql interactive mode. Here is how to source an sql file:
mysql>source sqlfile;If you see a web application wanting you to run an sql file, the two above commands can be used to accomplish that task. We have our table setup, so how do we check our fields? We do this by searching our table with queries.
Browsing MySQL tables with queries
Queries are one of the main features of any SQL database. They help us turn data in our tables into something useful. Most queries are done with the SELECT command. The SELECT command is fairly complex, and we're only going to look at three main forms of the command in this document.
## (Select all entries in a table)
SELECT * FROM table;
## (Select specific entries in a table)
SELECT * FROM table WHERE field=value;
## (Select specific fields)
SELECT field1,field2,field3 FROM table [WHERE field=value];
Let's take a quick look at the first form. It's relatively simple and gives you an overall look of your table. We'll go ahead and run it to see what data we have so far.
mysql>SELECT * FROM developers;+----+-------------+-----------------------+----------------+ | id | name | email | job | +----+-------------+-----------------------+----------------+ | 1 | Joe Smith | joesmith@gentoo.org | toolchain | | 2 | Jane Doe | NULL | Outsourced job | | 3 | John Doe | johndoe@gentoo.org | portage | | 4 | Chris White | chriswhite@gentoo.org | documentation | | 5 | Sam Smith | samsmith@gentoo.org | amd64 | +----+-------------+-----------------------+----------------+ 5 rows in set (0.00 sec)
We see both the data we inserted through INSERT and those inserted by LOAD DATA present. Now, let's say that we just want to see the record for Chris White. We can do so with the second form of select as shown below.
mysql>SELECT * FROM developers WHERE name = 'Chris White';+----+-------------+-----------------------+---------------+ | id | name | email | job | +----+-------------+-----------------------+---------------+ | 3 | Chris White | chriswhite@gentoo.org | documentation | +----+-------------+-----------------------+---------------+ 1 row in set (0.08 sec)
As expected, the specific entry that we were looking for has been selected. Now, let's say we only wanted to know the person's job and email address, not their name. We can do so with the third form of SELECT as shown here.
mysql>SELECT email,job FROM developers WHERE name = 'Chris White';+-----------------------+---------------+ | email | job | +-----------------------+---------------+ | chriswhite@gentoo.org | documentation | +-----------------------+---------------+ 1 row in set (0.04 sec)
This method of selection is a lot easier to manage, especially with larger amounts of information, as we'll see later on. Right now, being the root mysql user, we have unlimited permissions to do what we wish with the MySQL database. In a server environment, a user with such privileges can be quite problematic. In order to control who does what with the databases, we setup privileges.
MySQL privileges
Privileges are what kind of access users have to databases, tables, pretty much anything. Right now in the gentoo database, the MySQL root account is the only account that can access it, given its permissions. Now, let's create two somewhat generic users, guest and admin, who will access the gentoo database and work with the information in it. The guest account will be a restricted one. All it will be able to do is get information from the database, and that's it. admin will have the same control as root, but only for the gentoo database (not the main mysql databases).
Creating users
The CREATE USER SQL statement will define users and set the authentication method, commonly by password but other plugins may be available.
An example CREATE USER command is:
CREATE USER '[user]'@'[host]' IDENTIFIED BY '[password]';
user is the name of the user and host is the hostname the user will be accessing from. In most cases, this will be localhost. To create our users for this example:
(admin)
mysql>CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
(guest)
mysql>CREATE USER 'guest'@'localhost' IDENTIFIED BY 'password';
A host of 'localhost' does not mean DNS localhost (127.0.0.1) to MySQL. Instead, it refers to the UNIX socket connection and not TCP/IP.
Granting privileges with GRANT
Let's have a closer look at this somewhat simplified format of the GRANT command.
GRANT [privileges] ON database.* TO '[user]'@'[host]';
First we have the privileges we wish to assign. With what we've learned so far, here are some of the privileges you can set:
ALL- Gives the all privilege control for the databaseCREATE- Allows users to create tablesSELECT- Allows users to query tablesINSERT- Allows users to insert data into a tableSHOW DATABASES- Allows users to see a list of databasesUSAGE- User has no privilegesGRANT OPTION- Allows users to grant privileges
If you're running MySQL to communicate data to a web application,
CREATE , SELECT , INSERT (discussed here), DELETE and UPDATE (for further information look up the MySQL Reference Manual - GRANT and REVOKE Syntax section) are the only permissions you will most likely need. A lot of people make the mistake of granting all permissions when it's not really necessary. Check with the application developers to see if such permissions will cause issues with general operation.For our admin user, ALL will do. For the guest user, SELECT will be sufficient for read only access. database is the database we wish the user to have these permissions on. In this example, gentoo is the database. The .* means all tables. If you wanted to, you could apply per table access.
mysql>GRANT ALL ON gentoo.* TO 'admin'@'localhost';mysql>GRANT SELECT ON gentoo.* TO 'guest'@'localhost';Now that we have the users setup, let's test them out. First we quit mysql by typing quit at the command prompt:
mysql>quitWe're now back at the console. Now that we have our users setup, let's go ahead and see what they can do.
Testing user permissions
We shall now attempt to login as the guest user. Currently, the guest user has SELECT only privileges. This basically comes down to being able to search and nothing more. Go ahead and login with the guest account.
user $mysql -u guest -h localhost -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Now we should test the user restriction(s). Let's switch to the gentoo database:
mysql>USE gentoo;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
Now let's try to do something we are not supposed to. We'll attempt to create a table.
mysql>CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
As you can see, this function fails, as our user does not have the appropriate access. However, one access we did grant is the SELECT statement. Let's go ahead and try that:
mysql>SELECT * FROM developers;+-------------+-----------------------+----------------+ | name | email | job | +-------------+-----------------------+----------------+ | Joe Smith | joesmith@gentoo.org | toolchain | | John Doe | johndoe@gentoo.org | portage | | Chris White | chriswhite@gentoo.org | documentation | | Sam Smith | samsmith@gentoo.org | amd64 | | Jane Doe | NULL | Outsourced job | +-------------+-----------------------+----------------+ 5 rows in set (0.00 sec)
The command succeeds, and we're given a glimpse of what user permissions can do. We did, however, create an admin account as well. This was created to show that even all permissions granted users can still have limitations. Go ahead and quit MySQL and login as the admin.
mysql>quitBye
user $mysql -u admin -h localhost -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
To begin, we'll try creating a new database with our admin user. This admin user will have access similiar to the root MySQL account, and will be able to do any kind of modification to the gentoo database it chooses. This will test the user's access to the main MySQL database. Remember ealier that we only set permissions to a specific database.
mysql>CREATE DATABASE gentoo2;ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'
Indeed, the admin user cannot create databases on the main MySQL database, despite all his permissions on the gentoo database. However, we're still able to use the admin account to modify the gentoo database, as shown here by this example data insertion.
mysql>USE gentoo;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> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
Query OK, 1 row affected (0.08 sec)
The admin user can access the database as they please. Now sometimes, we need to get rid of user permissions. This could be anything from a problematic user to a retired employee. Let's take a look at how to disable user permissions with the REVOKE command.
Removing user access with the REVOKE command
The REVOKE command lets us deny access to a user. We can either deny full access, or specific access. In fact, the format is very similiar to GRANT .
REVOKE [privileges] ON database.* FROM '[user]'@'[host]';
Options here are explained in the GRANT command section. In this section however, we're going to deny full access to a user. Let's say we find out the guest account is causing some problems security wise. We decide to revoke all privileges. We login as root and do the needful.
mysql>REVOKE ALL ON gentoo.* FROM 'guest'@'localhost';Query OK, 0 rows affected (0.00 sec)
In this case, user access is simple, so per database revoking is not a problem. However, in larger cases, you would most likely be using *.* instead of gentoo.* to remove user access to all other databases.
Now let's quit and attempt to login as a guest user.
user $mysql -u guest -h localhost -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Although we're able to login, our access to gentoo is now gone.
mysql>USE gentoo;ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
And our problematic user is no longer able to access the gentoo database. Please note that the user was still able to login. That is because they remain in the main MySQL database. Let's take a look at how to completely remove an account with DROP USER.
Removing accounts
Make sure you're logged in as root.
DROP USER will delete the record in the user table and all privilege tables. Let's go ahead and do that:
mysql>DROP USER 'guest'@'localhost';Query OK, 1 row affected (0.07 sec)
It seems to have worked OK. Let's test by logging out and attempting to login as our guest user.
mysql>quitBye
user $mysql -u guest -h localhost -pEnter password: ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)
Our user is now successfully deleted!
Conclusion
While this guide focuses mainly on setting up MySQL on the command line, a few alternatives are available in GUI form:
- phpMyAdmin - Popular php based MySQL administration tool.
This ends the MySQL introductory tutorial. I hope this gives you a better understanding of the basics behind MySQL and getting a database set up.
This page is based on a document formerly found on our main website gentoo.org.
The following people contributed to the original document: Chris White, Shyam Mani, Xavier Neys
They are listed here because wiki history does not allow for any external attribution. If you edit the wiki article, please do not add yourself here; your contributions are recorded on each article's associated history page.