Mysql
De whats Wiki
COMANDES
mysqldum --all-databases > base-de-dades.sql
VISUALITZAR VARIABLES MYSQLD
mysqladmin variables --user=root --password=xxxx
VISUALITZAR PROCESSOS QUE ESTÀ DUENT A TERME
mysqladmin processlist
POSAR SEGURETAT A UNA INSTAL·LACIÓ DEL MYSQL
mysql_secure_installation
Loguejar les consultes que es llancen des de el terminal (rollo la comanda script)
mysql> \T fitxer.log Logging to file 'fitxer.log'
Aturar el logueig
mysql> \t Outfile disabled.
Replicació Master-Slave
Master
log-bin = mysql-bin server-id=1
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'secretoreplicacion'; mysql> FLUSH PRIVILEGES;
Slave
log-bin = mysql-bin server-id=2
mysql> SLAVE STOP mysql> CHANGE MASTER TO MASTER_HOST='192.168.4.1', MASTER_USER='slave', MASTER_PASSWORD='secretoreplicacion', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1284, MASTER_PORT=3306; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
Notes
Per tal de configurar master-master, cal definir el autoincrement que no vagi de +1
Rendiment
Activar el log de les querys que no tenen indexos i les que obtenen tots els resultats de la taula
set global log_queries_not_using_indexes=1;
Case sensitive
set-variable=lower_case_table_names=1
CHARSET
set-variable=default-character-set=utf8 set-variable=default-collation=utf8_unicode_ci set-variable=collation_server=utf8_unicode_ci
Per forçar que els noms estiguin en utf8
init-connect = 'SET NAMES utf8'
Activar event scheduler
Posar en el mysql client:
SET GLOBAL event_scheduler=on;
O en el fitxer de configuració
event_scheduler=on
Cal afegir permisos per utilitzar-lo
GRANT EVENT ON database.* TO user@host;
Per crear l'event
mysql> create event primer_evento -> on schedule every 5 second -> on completion preserve -> enable -> do insert into cron(data) values (NOW());
SQL
Crear una taula amb l'estructura d'una altre
CREATE TEMPORARY TABLE importacio_prova_ohdim SELECT * FROM prova_ohdim_tmp3 limit 0;
Carregar dades eficientment
"LOAD DATA LOCAL INFILE '/tmp/filetoload.csv' REPLACE INTO TABLE prova_ohdim_tmp3 FIELDS TERMINATED BY ';' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\n';
Mostrar el detall de les taules
SHOW FULL COLUMNS FROM agenda;
Fer consultes sense passar per la cache
select SQL_NO_CACHE count(*), pk from channels;
BUSCAR
select * from alias where address like '%bewalivurreoc@correu.vilaweb.%';
Veure el detall d'una consulta
explain select * from spanish order by rand() limit 1;
MODIFICAR L'ESTRUCTURA DE LES TAULES
Afegir una clau primària al camp id_usuari
alter table usuaris add primary key (id_usuari);
Afegir un camp a la taula usuaris
ALTER TABLE usuaris ADD id_usuari INT(10) UNSIGNED NOT NULL FIRST;
Modificar un camp de la taula
ALTER TABLE noticies CHANGE Autor Autor INT(100) UNSIGNED NOT NULL AUTO_INCREMENT;
Eliminar una clau primària
alter table comentaris drop primary key;
Afegir una clau forana
alter table articles add foreign key (Id_usuari) references usuaris (Id_usuari);
MODIFICAR EL CONTINGUT DE LES TAULES
Modificar el valor d'una tupla
update general set Titol='$ins_titol',Cos='$ins_cos' where Id='$ins_id';
Inserir noves tuples a la taula
insert into general (Titol,Cos,Data) values ('$ins_titol','$ins_cos',now());
ELIMINAR
Eliminar taula
drop table usuaris;
Eliminar tuples
DELETE FROM tbl_name WHERE 1>0;
Eliminar usuari
DELETE FROM mysql.user WHERE User = 'mythtv'; o DROP USER $NAME@localhost;
PRIVILEGIS
Donar privilegis a un usuari
GRANT ALL ON correu.passwd TO postfix@localhost IDENTIFIED BY "pass_per_l'user_postfix";
Nomès podrà fer selects
GRANT SELECT ON * . * TO "pregoner"@"ocellot"IDENTIFIED BY "*******
Canviar pass de root
SET PASSWORD FOR root@localhost=password('la_nova_contrasenya');
FLUSH PRIVILEGES;
IMPORTANT: Per fer això bé, cal fer-ho així
mysqladmin -u root password 'password';
Crear un altre usuari administrador
create user admin@localhost identified by 'password'; grant all on *.* to admin@localhost with grant option;
Obligar utilitzar ssl
GRANT ALL PRIVILEGES ON remote.* TO 'user'@'othernetwork' REQUIRE SSL;
Afegir permisos per stored procedures
grant select, insert, update on mysql.proc to collabREte@localhost;
Exportar els permisos d'un usuari
SHOW GRANTS FOR user@localhost;
Exportar els permisos de tots els usuaris
SELECT DISTINCT CONCAT('SHOW GRANTS FOR , user, @, host, ;')
CREAR
Per crear una base de dades
create database nom_de_la_bd create database `co-prod`
Modificar AUTO_INCREMENT
ALTER TABLE ejemplo AUTO_INCREMENT=100;
RECUPERAR PASSWORD DE ROOT
Seguir els següents passos:
/etc/init.d/mysql stop
/usr/bin/mysqld_safe --skip-grant-tables &
mysql -u root mysql -e "UPDATE user SET Password=PASSWORD('password') WHERE user='root'; FLUSH PRIVILEGES;"
/etc/init.d/mysql stop
/etc/init.d/mysql start
ARXIU DE CONFIGURACIÓ
ESTABLIR UNA VARIABLE
set-variable=max_connect_errors=10000
ACTIVAR INNODB
Per saber si està activat o no, mirar el contingut de la variable have_innodb, si no ho està, es pot activar posant a l'arxiu de configuració:
innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1
Per a què comitegi els canvis al acabar una transacció
innodb_flush_log_at_trx_commit=1
Per crear un fitxer per taula
innodb_file_per_table = 1
ACTIVAR LOGUEJAR-HO TOT
Cal crear el fitxer, el mysqld no el crearà log=/var/log/mysql-tot.log
FER UN RELOAD DEL SERVIDOR
mysqladmin -p restart
BINARY LOGS
Llistar sentències d'un log binari
mysqlbinlog log_file
Carregar-les en una BD
mysqlbinlog log_file | mysql -p database
Llistar sentències executades en una base de dades concreta, d'un log binary
mysqlbinlog -d database log_file
Desactivar els checks de claus foranes
set foreign_key_checks=0
my.cnf
desactivar resolució dns
skip-name-resolve
32 bits, 2Gb dedicades, innodb
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 64 table_cache = 64 key_buffer = 64M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=16M max_heap_table_size=16M query_cache_size=64M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=3 log-error=/mysql/error.log innodb_data_home_dir = /data01/data innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 768M innodb_additional_mem_pool_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_lock_wait_timeout = 50 innodb_flush_method=O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_thread_concurrency = 8
32 bits, 4Gb dedicades, innodb
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 64 table_cache = 64 key_buffer = 64M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=16M max_heap_table_size=16M query_cache_size=64M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=3 log-error=/mysql/error.log innodb_data_home_dir = /data01/data innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size =2048M innodb_additional_mem_pool_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_lock_wait_timeout = 50 innodb_flush_method=O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_thread_concurrency = 8
32 bits, 8Gb dedicades, innodb
Per 8Gb comença a ser necessari canviar a un sistema de 64 bits.
64 bits, 8Gb dedicades, innodb
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 128 table_cache = 128 key_buffer = 64M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=32M max_heap_table_size=32M query_cache_size=128M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=2 log-error=/mysql/error.log innodb_data_home_dir = /data01/data innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 5120M innodb_additional_mem_pool_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_lock_wait_timeout = 50 innodb_flush_method=O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_thread_concurrency = 8
64 bits, 16Gb dedicades, innodb
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 128 table_cache =128 key_buffer = 64M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=32M max_heap_table_size=32M query_cache_size=128M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=2 log-error=/mysql/error.log innodb_data_home_dir = /data01/data innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 12288M innodb_additional_mem_pool_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_lock_wait_timeout = 50 innodb_flush_method=O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_thread_concurrency = 12
64 bits, 32Gb dedicades, innodb
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 64 table_cache = 64 key_buffer = 64M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=32M max_heap_table_size=32M query_cache_size=128M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=2 log-error=/mysql/error.log innodb_data_home_dir = /data01/data innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size =24676M innodb_additional_mem_pool_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_lock_wait_timeout = 50 innodb_flush_method=O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_thread_concurrency = 16
