(SGBD Livre)
O MySQL Server é um SGBD (Sistema Gerenciador de Banco de Dados).
Instalação Server
# apt-get install mysql-server
PARA ENTRAR NO AMBIENTE MYSQL
# mysql -u root -p (entrar no ambiente mysql)
# mysql -u root -D DATABASE -p (entrar no ambiente mysql na database chamada "DATABASE")
DENTRO DO AMBIENTE MYSQL
mysql> show databases;
mysql> use database_name;
mysql> show tables;
mysql> show tables status; (status da tabela)
mysql> show table status from database_name;
mysql> show table status from database_name like 'table_name';
mysql> show columns from TABLE;
COMANDOS SQL
# mysqlshow -p (mostra as databases do MYSQL)
# mysqlshow DATABASE -p (mostra as tabelas da database chamada DATABASE)
# mysql -u=root -p -h localhost -D database_name (teste de conexão)
SQL via Shell
# mysql -u root -p -D DATABASE -e "select * from TABLE"
EXPORTANDO O RESULTADO PARA UM ARQUIVO
# mysql -u root -p -D DATABASE -e "select * from TABLE" > /tmp/teste_outfile_mysql.txt (esse comando deve ser usado no shell [bash])
mysql> select * from TABLE INTO OUTFILE "/tmp/teste_outfile_mysql.txt"; (esse comando deve ser usado dentro do ambiente do MySQL, ou seja, dentro do prompt de comandos do MySQL)
BACKUP
# /etc/init.d/mysql stop
# tar -zcvf database_name.tar.gz /var/lib/mysql/database_name_dir/
# /etc/init.d/mysql start
ou
# mysql -u root -p -e -B database_name > database_name.sql
ou
# mysqldump -u root -p -e -B database_name > database_name.sql
# mysqldump -u root -p -l -e -B database_name > database_name.sql (antes de realizar o dump, faz um lock [trava] todas as tabelas da database que foi especificada)
# mysqldump -u root -p -x -e -B database_name > database_name.sql (antes de realizar o dump, faz um lock [trava] todas as tabelas de todas as databases do SGBD)
ou (melhor)
# mysqldump -u root -p -e --single-transaction -B database_name > database_name.sql (a opção --single-transaction deveria sempre ser especificada com o mysqldump e na maioria dos casos é muito melhor do que usar as opções "-l" ou "-x", pois ele faz um snapshot/fotografia da database para o disco antes de fazer o dump. Isso fará que qualquer operação de escrita feita na database durante o backup nao será backpeada. Assim, o dump que será feito do snapshot e não diretamente da database. Não se pode usar o "--single-transaction" com a opção "-x")
ou (especificando o charset)
# mysqldump -u root -p -e --single-transaction --default-character-set UTF8 -B database_name > database_name.sql
ou (compactando)
# mysqldump -u root -p -e --single-transaction --default-character-set UTF8 -B database_name | gzip > database_name.sql.gz ()
# mysqldump -u root -p -e --single-transaction --all-databases | gzip > all-databases.sql.gz ()
# mysqldump -u root -p --databases database_one database_two > two_databases.sql ()
# mysqldump -u root -p --compatible=mysql4 database_name > database_name.sql (modo de compatibilidade. Isso pode ser usado quando se quer migrar uma base de uma MySQL para outro com versão diferente ou quando apresenta erro de encoding)
Obs: existem as opcao --opt, --lock-tables (-l) e --lock-all-tables (-x) que devem ser usados com cuidado. Esses opcoes farao um "lock" (travamento) nas tabelas enquanto elas estao sendo copiadas para o disco, fazendo com que qualquer acesso para a database seja "congelada" enquanto o dump estive rodando.
RESTAURANDO O BACKUP
# mysql -u root -D DATABASE_NAME -p < database_name.sql (restaura a database chamada DATABASE_NAME inserindo um script chamado "database_name.sql". Vale ressaltar que a database ja deve existir no MySQL)
# gunzip -c database_name.sql.gz | mysql -u root -D DATABASE_NAME -p (restaura a database chamada DATABASE_NAMEi, inserindo um script chamado "database_name.sql.gz". Vale ressaltar que a database ja deve existir no MySQL e também que ela está compactada)
# gunzip < database_name.sql.gz | mysql -u root -D DATABASE_NAME -p (restaura a database chamada DATABASE_NAMEi, inserindo um script chamado "database_name.sql.gz". Vale ressaltar que a database ja deve existir no MySQL e também que ela está compactada)
# mysql --one-database DATABASE_NAME < all_databases.sql ()
CHARSET
mysql> status; (dentre várias informações, mostra o CHARSET usado no servidor, na database, no cliente e na conexao do MYSQL)
mysql> show character set; (mostra os tipos de CHARSET que se pode usar no MySQL)
mysql> show charset; (idem)
mysql> charset utf8; (muda o charset para UTF8)
mysql> \C utf8; (idem)
mysql> \C (um help que mostra a sintaxe para mudar o CHARSET)
mysql> SHOW COLLATION LIKE 'latin1%';
# mysql -u root -p123123 -h 192.168.1.27 -D mail --default-character-set LATIN1 -e "select Account, UserEmail, UserEmail2, UserFirstName, UserLastName from Abook_a where Account = '[email protected]' order by UserEmail desc"
# mysqldump -u root -p -x -e --default-character-set UTF8 -B database_name > database_name.sql
LOCALIZANDO USUARIOS DO MYSQL
mysql> use mysql (entra na database chamada "mysql")
mysql> SELECT * FROM user;
mysql> SELECT * FROM db;
CRIANDO USUARIOS OU CONCENDENDO DIREITOS DE ACESSO
mysql> GRANT ALL PRIVILEGES ON database.table TO 'username'@'host' IDENTIFIED BY 'password'; (segue esse padrao)
mysql> GRANT SELECT ON database.table TO 'username'@'host' IDENTIFIED BY 'password'; (somente leitura)
mysql> GRANT ALL PRIVILEGES ON database.* TO 'username'@'host' IDENTIFIED BY 'password'; (segue esse padrao. O "*" significa todas as tabelas)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password'; (segue esse padrao. O "*.*" significa todas as databases e todas as tabelas. ja o "%" significa qualquer IP)
mysql> GRANT ALL PRIVILEGES ON financeiro.* TO 'admin_financeiro'@'172.16.0.3' IDENTIFIED BY '123456'; (exemplo)
mysql> FLUSH PRIVILEGES;
mysql> GRANT SELECT ON database.table TO 'username'@'host' IDENTIFIED BY 'password'; (somente leitura)
mysql> GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON database.table TO 'username'@'host' IDENTIFIED BY 'password'; (segue esse padrao)
Obs: os usuarios criados e seus privilegios ficam na database "mysql" na tabela "user".
REMOVENDO DIREITOS DE ACESSO
mysql> REVOKE ALL PRIVILEGES ON database.table FROM 'username'@'host'; (segue esse padrao)
mysql> REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host'; (segue esse padrao. O "*" significa todas as tabelas)
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'%'; (segue esse padrao. O "*.*" significa todas as databases e todas as tabelas. já o "%" significa qualquer IP)
mysql> REVOKE ALL PRIVILEGES ON financeiro.* FROM 'admin_financeiro'@'172.16.0.3'; (exemplo)
mysql> FLUSH PRIVILEGES;
Obs: os usuarios criados e seus privilegios ficam na database "mysql" nas tabelas "user" e "db".
REMOVENDO USUARIOS
mysql> DELETE FROM mysql.user WHERE user='username'; (segue esse padrao)
mysql> DELETE FROM mysql.user WHERE user='admin_financeiro'; (exemplo)
mysql> FLUSH PRIVILEGES;
COLOCANDO UMA BASE EM READ-ONLY
mysql> REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host'; (segue esse padrao. O "*" significa todas as tabelas)
mysql> GRANT SELECT ON database.* TO 'username'@'host'; (não há necessidade de colocar senha, pois o usuário não foi removido com o comando anterior, foi somente retirada todas as permissões/privilégios)
mysql> FLUSH PRIVILEGES;
DEFINIÇÃO DE SENHA PARA UM USUARIO DO MYSQL
# mysqladmin -u username password new-password (quando não existe uma senha definida)
# mysqladmin -u username -pold-password password new-password (quando existe uma senha definida)
# mysqladmin flush-privileges (VERIFICAR SE É NECESSÁRIO: ativa os UPDATES anterior)
ou
mysql> use mysql (entra na database chamada "mysql")
mysql> UPDATE table SET Password=PASSWORD('new-password') WHERE user='username'; (segue esse padrao)
mysql> UPDATE user SET Password=PASSWORD('123456') WHERE user='root'; (exemplo)
mysql> FLUSH PRIVILEGES; (ativa o UPDATE anterior)
DEFINIÇÃO DE SENHA MD5 PARA UM USUARIO (muito usado em CMS como JOOMLA, DRUPAL ETC)
mysql> UPDATE users SET pass = MD5('12345678') WHERE uid=1; (muitos sistema com o Drupal, Joomla etc usam o MD5 para armazenar a senha)
Resolvendo Problemas
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; (esse comando concede privilegios de acesso um usuario quando o mesmo estiver acessando remotamente. Caso nao se execute esse comando um erro parecido como esse surgira: "ERROR 1130 (00000): Host 'x.x.x.x' is not allowed to connect to this MySQL server")
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION; (exemplo)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.1.7' IDENTIFIED BY '' WITH GRANT OPTION; (mais seguro, pois especifica um único IP de origem com direitos de acesso remoto)
COMANDOS IMPORTANTES
# mysqladmin variables -u root -p (mostra as variáveis relacionadas ao MySQL [buffers, threats, memory etc])
# mysqld --verbose --help (mostra os valores padrão das opções do Mysql Server, sempre verificando os valores setados nos arquivos de configuração)
# mysqld --no-defaults --verbose --help (idem, contudo não verifica os arquivos de configuração)
MARIADB NO CENTOS 7
# yum install mariadb mariadb-server
# chown mysql.mysql /var/lib/mysql -R
# systemctl enable mariadb
# systemctl start mariadb
# mysql_secure_installation
TUNNING
# mysql -u root -p
set global net_buffer_length= 1048576; (--Set network buffer length to a large byte number)
set global max_allowed_packet=1000000000; (--Set maximum allowed packet size to a large byte number)
SET foreign_key_checks = 0; (--Disable foreign key checking to avoid delays,errors and unwanted behaviour)
http://stackoverflow.com/questions/13717277/how-can-i-import-a-large-14-gb-mysql-dump-file-into-a-new-mysql-database
VERIFICAR E REPARAR
# mysqlcheck -Aa --auto-repair -u root -p (-A = --all-databases [todas as bases], -a = --analyze [todas as tabelas] e --auto-repair [se uma tabela estiver corrompida, automaticamente tentará consertá-la])
# mysqlcheck -Ao --auto-repair -u root -p (idem, só que -o = optimize [otimizará as tabelas])
BYPASS NA AUTÊNTICAÇÃO 1
/root/.my.cnf
[client]
user=root
password=
[mysql]
user=root
password=
[mysqldump]
user=root
password=
[mysqldiff]
user=root
password=
BYPASS NA AUTÊNTICAÇÃO 2
/etc/.my.cnf
skip-grant-tables
LOGS
[mysqld]
general_log_file = /var/log/mariadb/mariadb-debug.log (logs relacionado ao que é enviado pelos cliente)
general_log = 1 (registra logs relacionados as consultas, inserções, atualizações, acesso etc que são feitos nas bases de dados e no MySQL)
---
log_slow_queries = /var/log/mariadb/mariadb-slow.log
long_query_time = 2 (consultas [selects] acima de 2 segundos serão registradas e detalhadas)
---
[mysqld_safe]
log-error = /var/log/mariadb/mariadb.log (os logs de erro irão para o "/var/log/mariadb/mariadb.log". Registra os acertos e problemas encontrados ao iniciar, executar ou parar o mysqld. Devido a registro os acertos e problemas é interessante colocar em "mariadb.log" e não em mariadb_error.log)
#syslog (VER: geralmente vem comentada, mas se habilitada registra os logs de erro no syslog)
AUMENTO DE DESEMPENHO
[mysqld]
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
innodb-buffer-pool-instances = 16 (16 não é o valor default)
#max_connections = 100 (VER)
#table_cache = 64 (VER)
#thread_concurrency = 10 (VER)
query_cache_limit = 1M
query_cache_size = 16M
---
[mysqldump]
max_allowed_packet = 16M
---
[isamchk]
key_buffer = 16M
VÁRIAS CONFIGURAÇÕES
[mysqld]
character_set_server = utf8
bind-address = 10.1.1.5 (IP que o MySQL vai trabalhar)
user = mysql
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp
language = /usr/share/mysql/english
lc-messages-dir = /usr/share/mysql
chroot = /var/lib/mysql/
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
[client]
port = 3307
CONEXÕES MYSQL
mysql> SHOW STATUS LIKE 'Threads_connected';
mysql> SHOW STATUS where Variable_name like '%connect%';
mysql> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
mysql> select @@max_connections;
mysql> SHOW STATUS where Variable_name='Com_show_status';
mysql> show global status like "%Max_used%";
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
mysql> select version();show variables like "%max_connections%";show global status like "%Max_used%";
mysql> show status like "%thread%";
mysql> show global status like "%Aborted%";
mysql> show variables like "%thread_cache%";
mysql> SHOW VARIABLES;
mysql> select * from information_schema.user_privileges;
mysql> show databases;
mysql> SELECT @@profiling;
mysql> SHOW FULL PROCESSLIST;
mysql> SHOW PROCESSLIST;
mysql> SHOW PROFILE;
mysql> SHOW PROFILE FOR QUERY 1;
mysql> SHOW PROFILE FOR QUERY 2;
# vim /etc/mysql/my.cnf
max_connections = 2048
mysql> show full processlist\G;
SQL via Shell
select * from information_schema.user_privileges;
TAMANHO DAS BASES DE DADOS
mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
Referências Bibliográgicas