Tutorial Mysql, Comandos imprescindibles para administrarlo
En este tutorial de Mysql, vamos a ver lo que mínimo que hay que saber para tener unas nociones de administrar una base de datos de forma básica.
Para esto partimos de la base de que tenemos Mysql instalado y arrancado en nuestra maquina.
1 – Como poner la password a root de Mysql
# mysqladmin -u root password NUEVOPASS
2- Cambiar Password Mysql de root:
Si queremos cambiar el password de mysql siendo el antiguo 123456 por abcdef
# mysqladmin -u root -p 123456 password 'abcdef'
3- Comprobar si Mysql está arrancado:
# mysqladmin -u root -p ping Enter password: myqsld is alive
4- Como comprobar que versión de mysql tengo:
# mysqladmin -u root -p version Enter password: mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686 Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.5.28 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 days 14 min 45 sec Threads: 2 Questions: 36002 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059
5 – Para saber el estado del servidor Mysql.
# mysqladmin -u root -ptmppassword status Enter password: Uptime: 606704 Threads: 2 Questions: 36003 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059
6 – Comprobar el valor y estado de todas las variables del servidor Mysql.
# mysqladmin -u root -p variables Enter password: +---------------------------------------------------+----------------------------------------------+ | Variable_name | Value | +---------------------------------------------------+----------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | +---------------------------------------------------+----------------------------------------------+
7- Como ver procesos activos de mysql:
# mysqladmin -u root -p processlist Enter password: +-------+---------+-----------------+---------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+---------+-----------------+---------+---------+------+-------+------------------+ | 18001 | rsyslog | localhost:38307 | rsyslog | Sleep | 5590 | | | | 18020 | root | localhost | | Query | 0 | | show processlist | +-------+---------+-----------------+---------+---------+------+-------+------------------+
8 – Crear Base de datos en Mysql:
# mysqladmin -u root -p create databasename Enter password:
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18027 Server version: 5.5.28 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | databasename | | mysql | | test | +--------------------+ 8 rows in set (0.01 sec) mysql>
9 – Como quitar una base de datos de mysql:
# mysqladmin -u root -p drop databasename Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'databasename' database [y/N] y Database "databasename" dropped
10 – Como recargar los privilegios en Mysql
# mysqladmin -u root -p reload; # mysqladmin -u root -p refresh
11 – 2 formas de como apagar mysql de forma segura:
mysqladmin -u root -p shutdown Enter password:
A través del script arranque/parada:
# /etc/init.d/mysqld stop # /etc/init.d/mysqld start
12 – Matar procesos zombis:
# mysqladmin -u root -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 5 | root | localhost | | Sleep | 14 | | | | 8 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+
Ahora ejecutamos un kill al ID del proceso en cuestión:
# mysqladmin -u root -p kill 5 Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 12 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+
13 – Conectar a un servidor mysql remoto:
# mysqladmin -h 172.16.25.126 -u root -p
14 – ejecutar comando servidor remoto:
# mysqladmin -h 172.16.25.126 -u root -p
15 – Arrancar y parar replicación de mysql en un servidor:
# mysqladmin -u root -p start-slave
# mysqladmin -u root -p stop-slave
Fuente: www.tecmint.com
Deja un comentario