De Wiki inetshell
Saltar a: navegación, buscar
(Mostrar estado del servidor)
(Cliente)
 
(No se muestra una edición intermedia del mismo usuario)
Línea 157: Línea 157:
 
ORDER BY (data_length + index_length) DESC;
 
ORDER BY (data_length + index_length) DESC;
  
 +
</pre>
 +
 +
== Conexion por SSL ==
 +
=== Cliente ===
 +
<pre>
 +
mysql -uroot -pPASSWORD -h 192.168.1.1 --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
 
</pre>
 
</pre>

Revisión actual del 13:27 13 jun 2019

Replicación[editar]

[1]

Respaldo[editar]

[2]

Utilizando AutoMySQLBackup[editar]

https://www.rosehosting.com/blog/how-to-install-and-configure-automysqlbackup/ https://serverfault.com/questions/268566/cant-backup-the-mysql-table-with-mysqldump-select-lock-tabl-command-denied-for

Instalación en CentOS 7[editar]

[3]

Operaciones MySQL[editar]

Crear base de datos y usuario con permisos[editar]

https://codex.wordpress.org/Installing_WordPress#Using_the_MySQL_Client

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5340 to server version: 3.23.54
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE DATABASE databasename;
Query OK, 1 row affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname"
    -> IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)
  
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye

Operaciones basicas[editar]

  • Crear base de datos:
CREATE DATABASE test;
  • Crear tabla:
CREATE TABLE phptest.Servers (hostname VARCHAR(64), ipv4 INT UNSIGNED, description VARCHAR(64), last_update DATE);
  • Crear usuario:
CREATE USER john IDENTIFIED BY 'password';
  • Actualizar usuario:
UPDATE USER SET PASSWORD=PASSWORD('new_password') WHERE user="john";
  • Eliminar usuario:
DROP USER 'john';
  • Listar usuarios:
SELECT user, host FROM mysql.user;
  • Agregar privilegios a usuario para una tabla:
GRANT ALL PRIVILEGES ON database_name.table_name to 'user'@'host';
  • Agregar privilegios a usuario para toda tabla de una base:
GRANT ALL PRIVILEGES ON database_name.* to 'user'@'host';
  • Revocar privilegios a un usuario:
REVOKE type_of_permission  ON database_name.* FROM 'user'@'host';
  • Actualizar privilegios:
FLUSH PRIVILEGES;

SSL / TLS[editar]

https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/

https://mariadb.com/kb/en/library/securing-connections-for-client-and-server/

http://delatbabel.blogspot.com/2017/03/lets-encrypt-ssl-recipes-mysql-with.html

certbot certonly --standalone --preferred-challenges http -d mysite.com --non-interactive --agree-tos -m [email protected]

cp /etc/letsencrypt/live/mysite.com/*.pem /var/lib/mysql
chown mysql.mysql /var/lib/mysql/*.pem
chmod 600 /var/lib/mysql/*.pem

vi /etc/my.cnf

[mysqld]
# SSL
ssl_ca=/var/lib/mysql/chain.pem
ssl_cert=/var/lib/mysql/cert.pem
ssl_key=/var/lib/mysql/privkey.pem

service mariadb restart

check
SHOW VARIABLES LIKE 'have_ssl';

Limitar usuarios a un segmento de red[editar]

https://stackoverflow.com/questions/11742963/how-to-grant-remote-access-to-mysql-for-a-whole-subnet

GRANT ALL ON *.* to [email protected]'192.168.1.%' IDENTIFIED BY 'your-root-password';

Mostrar estado del servidor[editar]

https://stackoverflow.com/questions/1504608/how-to-measure-current-load-of-mysql-server

SHOW GLOBAL STATUS;

https://stackoverflow.com/questions/7432241/mysql-show-status-active-or-total-connections

show status where `variable_name` = 'Threads_connected';
show processlist;

Mostrar tamaño de tablas[editar]

https://tableplus.io/blog/2018/04/mysql-get-size-of-tables.html

  • Size of a specific table:
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = ‘db_name’
    AND table_name = ‘table_name’;
  • Size of all tables, descending order:
SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Conexion por SSL[editar]

Cliente[editar]

mysql -uroot -pPASSWORD -h 192.168.1.1 --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem