View
31
Download
0
Category
Preview:
Citation preview
MySQL Administração Backup & Recovery Tuning Alta Disponibilidade
Inclui Percona e MariaDB
Ricardo Portilho Proniricardo@nervinformatica.com.brNerv Informática Ltda.
Esta obra está licenciada sob a licençaCreative Commons Atribuição-SemDerivados 3.0 Brasil.Para ver uma cópia desta licença, visite http://creativecommons.org/licenses/by-nd/3.0/br/.
22
Produtos utilizados no Treinamento● Oracle Enterprise Linux 7.7● MySQL 5.7.28 e 8.0.13● Percona Server 5.7.28● MariaDB 10.4.10● Oracle VirtualBox 6.0.14
33
Comandos no Treinamento
Comando com o usuário root:# mysql -u root -pNerv2019.
Comando no MySQL:mysql> SHOW DATABASES;
Adicionar texto a um arquivo:# vi /etc/my.cnf...log-bin=mysql-bin...
Algo deve ser alterado de acordo com sua máquina:# scp -Cr /root/ColdBackup/mysql/* root@192.168.0.99:/var/lib/mysql/
Quando algo dá errado propositalmente:O que aconteceu?
Todas as senhas são Nerv2019.
4
Mercado de Trabalho MySQL
4
55
Por que MySQL?
https://db-engines.com/en/ranking
66
DBA MySQLDBA MySQL e ...● Desenvolvedor● Administrador de Dados● Administrador de Sistema Operacional● DevOPS● Analista de Sistema● DBA de outro banco
77
Certificações
https://education.oracle.com/database/mysql/pFamily_406
88
Livros● MySQL 5.0 Certification Study Guide● High Performance MySQL
99
MySQL Blogs
1010
MySQL Blogs
1111
MySQL Blogs
1212
MySQL Blogs
1313
Fóruns
1414
Documentação
https://dev.mysql.com/doc/refman/5.5/en/index.htmlhttps://dev.mysql.com/doc/refman/5.6/en/index.htmlhttps://dev.mysql.com/doc/refman/5.7/en/index.htmlhttps://dev.mysql.com/doc/refman/8.0/en/
1515
MySQL Internals Manual
https://dev.mysql.com/doc/internals/en/
16
Produtos MySQL
16
17
História do MySQL
18
História do MySQL
1919
Open Source
2020
Open Source
2121
Open Source
2222
Produtos Community
2323
Produtos Enterprise
https://www.mysql.com/products/
2424
Produtos Enterprise
https://www.mysql.com/products/
2525
mysqlbackup
https://www.mysql.com/products/enterprise/backup.html
2626
Thread Pool
https://www.mysql.com/products/enterprise/scalability.html
2727
MySQL Enterprise Monitor
https://www.mysql.com/products/enterprise/monitor.html
2828
MySQL Enterprise Monitor - MySQL Query Analyzer
https://www.mysql.com/products/enterprise/monitor.html
2929
MySQL Enterprise Monitor - Database File IO
https://www.mysql.com/products/enterprise/monitor.html
3030
MySQL Enterprise Monitor - MySQL Replication Topology View
https://www.mysql.com/products/enterprise/monitor.html
3131
MySQL Enterprise Monitor - MySQL Cluster Topology View
https://www.mysql.com/products/enterprise/monitor.html
3232
MySQL Enterprise Monitor - MySQL Replication Multi-Source
https://www.mysql.com/products/enterprise/monitor.html
3333
MySQL Enterprise Monitor - MySQL Replication Status
https://www.mysql.com/products/enterprise/monitor.html
3434
MySQL Enterprise Monitor - MySQL Enterprise Backup Dashboard
https://www.mysql.com/products/enterprise/monitor.html
35
Instalação
35
3636
Instalação – Repositório Padrão[root@CentOS-6 ~]# cat /etc/redhat-releaseCentOS release 6.10 (Final)[root@CentOS-6 ~]# yum -y install mysql-server...[root@CentOS-6 ~]# rpm -qa | grep mysqlmysql-libs-5.1.73-8.el6_8.x86_64mysql-server-5.1.73-8.el6_8.x86_64mysql-5.1.73-8.el6_8.x86_64[root@CentOS-6 ~]#
[root@CentOS-7 ~]# cat /etc/redhat-releaseCentOS Linux release 7.5.1804 (Core)[root@CentOS-7 ~]# yum -y install mysql-server...No package mysql-server available.[root@CentOS-7 ~]#
[root@CentOS-7 ~]# yum -y install mariadb-server[root@CentOS-7 ~]# rpm -qa | grep mariadb...mariadb-5.5.60-1.el7_5.x86_64mariadb-libs-5.5.60-1.el7_5.x86_64mariadb-server-5.5.60-1.el7_5.x86_64[root@CentOS-7 ~]#
3737
Red Hat / CentOS / OEL e MySQL / MariaDB
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/7.0_release_notes/chap-red_hat_enterprise_linux-7.0_release_notes-web_servers_and_services
3838
Instalação – Versão Específica
3939
Instalação – Repositórios MySQL
https://dev.mysql.com/downloads/
4040
Lab 1.1: Instalação MySQLEdite o arquivo /etc/yum.repos.d/public-yum-ol7.repo, e habilite o reposítório do MySQL 5.7.# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm# yum -y install mysql80-community-release-el7-3.noarch.rpm
# vi /etc/yum.repos.d/mysql-community.repo...# Enable to use MySQL 5.7[mysql57-community]name=MySQL 5.7 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/enabled=1gpgcheck=1gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql...[mysql80-community]name=MySQL 8.0 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/enabled=0gpgcheck=1gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql...
# yum -y install mysql-server# systemctl enable mysqld# ls -lh /var/lib/mysql# systemctl start mysqld# ls -lh /var/lib/mysql
4141
Lab 1.2: Instalação MySQLVerifique o Log do MySQL, e altere a senha do usuário root.# grep password /var/log/mysqld.log# /usr/bin/mysqladmin -u root -p password 'Nerv2019.'
Acesse o MySQL, e verifique o acesso.# mysql -u root -pmysql> EXIT;
Libere a porta do MySQL no Firewall.# firewall-cmd --zone=public --add-port=3306/tcp --permanent# firewall-cmd --reload# firewall-cmd --zone=public --permanent --list-ports
4242
Lab 1.3: Status MySQL
Verifique as formas de conexão com o MySQL.# mysql -u root -pmysql> STATUS;mysql> EXIT;
# mysqladmin -u root -pNerv2019. status
# tail /var/log/mysqld.log
# grep -B1 “ready for connections” /var/log/mysqld.log
4343
Lab 1.4: Client MySQL
Verifique as formas de conexão com o MySQL.# mysql -u root -pmysql> SHOW DATABASES;mysql> EXIT;
# mysql -pmysql> SHOW DATABASES;mysql> EXIT;
# mysql -u root -pNerv2019.mysql> SHOW DATABASES;mysql> EXIT;
# mysql -uroot -pNerv2019.mysql> SHOW DATABASES;mysql> EXIT;
mysql> prompt \U-\d-\R:\m:\s>mysql> SHOW DATABASES;mysql> USE mysql;mysql> EXIT;
# vi .my.cnf[client]password=Nerv2019.prompt='\U-\d-\R:\m:\\s>'
# mysql
4444
Lab 1.5: Client MySQL
Verifique as formas de execução de comandos no MySQL.# mysqlmysql> SHOW DATABASES;# mysql -e “SHOW DATABASES”# mysql -e 'SHOW DATABASES'# mysql -t -e 'SHOW DATABASES'# mysql -B -e 'SHOW DATABASES'# mysql -E -e 'SHOW DATABASES'# mysql -X -e 'SHOW DATABASES'
# mysql -e 'SHOW DATABASES' > databases.txt# cat databases.txt
# mysql -t -e 'SHOW DATABASES' > databases.txt# cat databases.txt
# echo 'SHOW DATABASES' > databases.sql# mysql -t < databases.sql# mysql -t < databases.sql > databases.txt# cat databases.txt
mysql> source databases.sql
Importe os bancos de dados de exemplo.# mysql < MYA.sql# mysql -v < MYA.sqlmysql> SHOW DATABASES;
4545
Lab 1.6: Client MySQL
Verifique as formas de execução de comandos no MySQL.mysql> SHOW STATUS;mysql> pager less -n -i -S -F -Xmysql> SHOW STATUS;
mysql> \Wmysql> \w
# export EDITOR=vi# mysqlmysql> SHOW DATABASES;mysql> editmysql> ;
mysql> tee audit.logmysql> SHOW DATABASES;mysql> notee# cat audit.log
mysql> system ls -lhmysql> \! ls -lh
mysql> help
4646
Engines
https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines
4747
Engines
Verifique as formas de execução de comandos no MySQL.mysql> CREATE DATABASE test;mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT);mysql> SHOW CREATE TABLE t1;
mysql> CREATE TABLE t2 (c1 INT) Engine=InnoDB;mysql> SHOW CREATE TABLE t2;
mysql> CREATE TABLE t3 (c1 INT) Engine=MyISAM;mysql> SHOW CREATE TABLE t3;
mysql> CREATE TABLE t4 (c1 INT) Engine=Memory;mysql> SHOW CREATE TABLE t4;
mysql> CREATE TABLE t5 (c1 INT) Engine=Archive;mysql> SHOW CREATE TABLE t5;
mysql> ALTER TABLE t5 Engine=InnoDB;mysql> SHOW CREATE TABLE t5;
mysql> SHOW ENGINES;
4848
Engines MySQL x Percona x MariaDB
4949
Releases MySQL x Percona x MariaDB
https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/
5050
MySQL x Percona Server
https://db-engines.com/en/ranking
5151
Percona Server
5252
Percona Server
5353
Percona Server
5454
Percona Server
https://www.percona.com/software/mysql-database/percona-server/benchmarks
5555
Percona Server
5656
Percona Server
https://www.percona.com/software/mysql-database/percona-server/feature-comparison
5757
Percona Server
https://www.percona.com/software/mysql-database/percona-server/feature-comparison
5858
Percona Server
https://www.percona.com/software/mysql-database/percona-server/feature-comparison
5959
Percona Server
https://www.percona.com/software/mysql-database/percona-server/feature-comparison
6060
Lab 2.1: Percona Server
Instale o Oracle VM VirtualBox.# yum -y install SDL kernel-uek-devel# wget https://download.virtualbox.org/virtualbox/6.0.14/VirtualBox-6.0-6.0.14_133895_el7-1.x86_64.rpm# yum -y install VirtualBox-6.0-6.0.14_133895_el7-1.x86_64.rpm
6161
Lab 2.2: Percona Server
No Oracle VM VirtualBox, clique em “File”, e em “Import Appliance”, e importe o arquivo /root/Linux.ova.
6262
Lab 2.3: Percona Server
6363
Lab 2.4: Percona Server
6464
Lab 2.5: Percona Server
6565
Lab 2.6: Percona Server
Verifique o IP da VM, e a acesse via ssh.# ip addr
Pelo nmtui, altere o hostname da VM para Percona.localdomain.
Instale o Percona Server.# yum -y install wget# wget https://www.percona.com/redir/downloads/percona-release/redhat/1.0-13/percona-release-1.0-13.noarch.rpm# yum -y install percona-release-1.0-13.noarch.rpm# yum search Percona-Server# yum -y install Percona-Server-server-57# systemctl enable mysqld# systemctl start mysqld# grep password /var/log/mysqld.log# mysqladmin -u root -p password 'Nerv2019.'# mysql -u root -pNerv2019.mysql> SHOW DATABASES;mysql> SHOW ENGINES;
Importe os bancos de dados de exemplo.# mysql -u root -pNerv2019. -e “SHOW DATABASES”# mysql -u root -pNerv2019. < MYA.sql# mysql -u root -pNerv2019. -e “SHOW DATABASES”
6666
MariaDB
https://en.wikipedia.org/wiki/MariaDB
6767
MariaDB
https://mariadb.com/kb/en/library/mariadb-vs-mysql-features/
6868
MariaDB
https://mariadb.com/kb/en/library/mariadb-vs-mysql-features/
6969
MariaDB
https://mariadb.com/kb/en/library/mariadb-vs-mysql-features/
7070
MariaDB
https://mariadb.com/kb/en/library/optimizer-feature-comparison-matrix/
7171
MariaDB
https://mariadb.com/kb/en/library/optimizer-feature-comparison-matrix/
7272
MariaDB
https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/
7373
MariaDB
https://mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/
7474
MariaDB
7575
MariaDB - Services
https://mariadb.com/services/
7676
MariaDB - Subscription
https://mariadb.com/pricing/
7777
MariaDB - Subscription
https://mariadb.com/pricing/
7878
MariaDB x mariadb.org x mariadb.com
https://mariadb.org/about/https://mariadb.org/about/#sponsorshttps://mariadb.org/about/#board
7979
MariaDB x mariadb.org x mariadb.com
https://mariadb.com/trademarks/
8080
Business Source License (BSL)
https://mariadb.com/bsl-faq-mariadb/
8181
Business Source License (BSL)
https://mariadb.com/projects-using-bsl-11/
8282
MariaDB - Parceiros
https://mariadb.com/kb/en/library/sql-diagnostic-manager-sqlyog/
8383
Lab 3.1: MariaDBNo Oracle VM VirtualBox, crie uma nova VM, com o nome “MariaDB”, importanto novamente o arquivo /root/Linux.ova.
8484
Lab 3.2: MariaDB
https://downloads.mariadb.org/mariadb/repositories
8585
Lab 3.3: MariaDB
Verifique o IP da VM, e a acesse via ssh.# ip addr
Pelo nmtui, altere o hostname da VM para MariaDB.localdomain.
Instale o MariaDB.# vi /etc/yum.repos.d/MariaDB.repo[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.4/rhel7-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1
# yum -y install MariaDB-server# systemctl enable mariadb# ls -lh /var/lib/mysql/# systemctl status mariadb# systemctl start mariadb# ls -lh /var/lib/mysql/# mysqlmysql> SHOW ENGINES;
Importe os bancos de dados de exemplo.# mysql -e “SHOW DATABASES”# mysql < MYA.sql# mysql -e “SHOW DATABASES”
86
Administração
86
8787
Lab 4.1: Estruturas Físicas e Lógicas
Crie um novo banco de dados.# mysqlmysql> SHOW TABLES;mysql> SHOW DATABASES;
mysql> CREATE DATABASE nerv;mysql> SHOW DATABASES;mysql> USE nerv;mysql> STATUS;mysql> SHOW TABLES;
mysql> SELECT * FROM employees.titles;mysql> select * from employees.titles;
mysql> SELECT * FROM EMPLOYEES.titles;mysql> SELECT * FROM employees.TITLES;
mysql> SHOW CREATE TABLE employees.titles;
# mysql employeesmysql> SHOW TABLES;
8888
Lab 4.2: Estruturas Físicas e LógicasVerifique as estruturas físicas dos MySQL.# ls -lh /var/lib/mysql/...drwxr-x---. 2 mysql mysql 4.0K Nov 30 10:22 employees-rw-r-----. 1 mysql mysql 13K Dec 1 19:21 ib_buffer_pool-rw-r-----. 1 mysql mysql 48M Dec 1 19:21 ib_logfile0-rw-r-----. 1 mysql mysql 48M Dec 1 19:21 ib_logfile1drwxr-x---. 2 mysql mysql 84 Nov 30 10:22 menageriedrwxr-x---. 2 mysql mysql 4.0K Nov 30 10:22 mysqldrwxr-x---. 2 mysql mysql 20 Dec 1 18:56 nervdrwxr-x---. 2 mysql mysql 8.0K Nov 30 08:47 performance_schemadrwxr-x---. 2 mysql mysql 4.0K Nov 30 10:22 sakiladrwxr-x---. 2 mysql mysql 8.0K Nov 30 08:47 sysdrwxr-x---. 2 mysql mysql 4.0K Nov 30 10:37 testdrwxr-x---. 2 mysql mysql 4.0K Nov 30 10:22 world...[root@MySQL-01 ~]#
# ls -lh /var/lib/mysql/nerv/-rw-rw----. 1 mysql mysql 65 Out 26 15:57 db.opt
# cat /var/lib/mysql/nerv/db.optdefault-character-set=latin1default-collation=latin1_swedish_ci
# ls -lh /var/lib/mysql/menagerie/# ls -lh /var/lib/mysql/mysql/
8989
Lab 4.3: Dicionário de Dados
Verifique o conteúdo das tabelas do banco mysql.# mysqlmysql> USE mysql;mysql> SHOW TABLES;mysql> SELECT * FROM db;mysql> SELECT * FROM user;
9090
Lab 4.4: Dicionário de Dados
Verifique o conteúdo das tabelas do banco information_schema.# mysqlmysql> USE information_schema;mysql> SHOW TABLES;mysql> SELECT * FROM TABLES;mysql> SELECT * FROM USER_PRIVILEGES;
http://dev.mysql.com/doc/refman/5.7/en/information-schema.html
9191
Lab 4.5: Dicionário de Dados
Verifique o conteúdo das tabelas do banco performance_schema.# mysqlmysql> USE performance_schema;mysql> SHOW TABLES;mysql> SELECT * FROM threads;mysql> SELECT * FROM users;
http://dev.mysql.com/doc/refman/5.7/en/performance-schema.htmlhttp://dev.mysql.com/doc/refman/5.7/en/performance-schema-table-index.htmlhttp://dev.mysql.com/doc/refman/5.7/en/sys-schema.html
9292
Lab 4.6: Variables, Status, Thread States
Verifique as Variables, Status, e Thread States.# mysqlmysql> SHOW VARIABLES;mysql> SHOW VARIABLES LIKE '%wait%';
mysql> SHOW STATUS;mysql> SHOW STATUS LIKE '%Thread%';
mysql> SHOW PROCESSLIST;mysql> SHOW FULL PROCESSLIST;
Variableshttp://dev.mysql.com/doc/refman/5.7/en/server-system-variables.htmlhttps://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
Status Variableshttp://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
Thread Stateshttp://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
9393
Variables
9494
Variables
9595
Lab 4.7: VariablesAltere uma Variable, com SET e com SET GLOBAL.# mysqlmysql> SHOW VARIABLES LIKE '%sort%';mysql> SET sort_buffer_size=524288; (OU SET @@local.sort_buffer_size=524288;)mysql> SHOW VARIABLES LIKE '%sort%'; (OU SELECT @@local.sort_buffer_size;)mysql> SELECT @@global.sort_buffer_size;mysql> EXIT;# mysqlmysql> SHOW VARIABLES LIKE '%sort%';mysql> SELECT @@local.sort_buffer_size;
# mysqlmysql> SHOW VARIABLES LIKE '%sort%';mysql> SET GLOBAL sort_buffer_size=524288; (OU SET @@global.sort_buffer_size=524288;)mysql> SHOW VARIABLES LIKE '%sort%'; (OU SELECT @@local.sort_buffer_size;)mysql> SELECT @@global.sort_buffer_size;mysql> EXIT;# mysqlmysql> SHOW VARIABLES LIKE '%sort%';
mysql> SET GLOBAL sort_buffer_size = DEFAULT;mysql> SET sort_buffer_size = DEFAULT;
mysql> SET global tmpdir='/var/lib/mysql/';
9696
Lab 4.8: Variables
Altere uma Variable pelo arquivo de parâmetros.# mysqlmysql> SHOW VARIABLES LIKE '%sort%';mysql> exit;
# vi /etc/my.cnf...[mysqld]sort_buffer_size = 2M...
# systemctl stop mysqld# systemctl start mysqld
# mysqlmysql> SHOW VARIABLES LIKE '%sort%';
mysql> SET GLOBAL sort_buffer_size=4M;O que aconteceu?
9797
Lab 4.9: Usuários e PermissõesCrie um usuário para conexão remota.mysql> CREATE USER 'ricardo'@'localhost' IDENTIFIED BY 'Nerv2019.';mysql> GRANT ALL PRIVILEGES ON *.* TO 'ricardo'@'localhost';mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'ricardo'@'localhost';mysql> DROP USER 'ricardo'@'localhost';
mysql> CREATE USER 'ricardo'@'192.168.0.%' IDENTIFIED BY 'Nerv2019.';mysql> CREATE USER 'ricardo'@'%.localdomain' IDENTIFIED BY 'Nerv2019.';mysql> CREATE USER 'ricardo'@'%' IDENTIFIED BY 'Nerv2019.';mysql> DROP USER 'ricardo'@'192.168.0.%';mysql> DROP USER 'ricardo'@'%.localdomain';mysql> DROP USER 'ricardo'@'%';
mysql> CREATE USER 'ricardo'@'192.168.0.102' IDENTIFIED BY 'Nerv2019.';mysql> GRANT SELECT ON world.city TO 'ricardo'@'192.168.0.102';mysql> GRANT SELECT ON nerv.* TO 'ricardo'@'192.168.0.102';mysql> SET PASSWORD FOR 'ricardo'@'192.168.0.102' = 'Nerv2019.';mysql> FLUSH PRIVILEGES;
mysql> SELECT * FROM information_schema.USER_PRIVILEGES;mysql> SELECT * FROM information_schema.SCHEMA_PRIVILEGES;mysql> SELECT * FROM information_schema.TABLE_PRIVILEGES;mysql> SELECT * FROM mysql.user;
Execute uma conexão remota.# mysql -u ricardo -pNerv2019. -h 192.168.0.103mysql> SHOW DATABASES;
9898
Usuários e PermissõesPrivilégioshttps://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
9999
Lab 4.10: Importação e Exportação
Exporte as tabelas do banco employees para texto.mysql> USE employees;mysql> SELECT * FROM departments INTO OUTFILE '/tmp/departments.txt';O que aconteceu?
mysql> SHOW VARIABLES LIKE 'secure_file_priv';mysql> SELECT * FROM departments INTO OUTFILE '/var/lib/mysql-files/departments.txt';mysql> SELECT * FROM dept_emp INTO OUTFILE '/var/lib/mysql-files/dept_emp.txt';mysql> SELECT * FROM dept_manager INTO OUTFILE '/var/lib/mysql-files/dept_manager.txt';mysql> SELECT * FROM employees INTO OUTFILE '/var/lib/mysql-files/employees.txt';mysql> SELECT * FROM salaries INTO OUTFILE '/var/lib/mysql-files/salaries.txt';mysql> SELECT * FROM titles INTO OUTFILE '/var/lib/mysql-files/titles.txt';
Outras opções:FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n';
100100
Lab 4.11: Importação e Exportação
Importe os arquivos texto para as tabelas do banco employees.mysql> CREATE DATABASE employees_dev;mysql> USE employees_dev;mysql> CREATE TABLE departments AS SELECT * FROM employees.departments WHERE 1=0;mysql> CREATE TABLE dept_emp AS SELECT * FROM employees.dept_emp WHERE 1=0;mysql> CREATE TABLE dept_manager AS SELECT * FROM employees.dept_manager WHERE 1=0;mysql> CREATE TABLE employees AS SELECT * FROM employees.employees WHERE 1=0;mysql> CREATE TABLE salaries AS SELECT * FROM employees.salaries WHERE 1=0;mysql> CREATE TABLE titles AS SELECT * FROM employees.titles WHERE 1=0;
mysql> SET foreign_key_checks = 0;mysql> LOAD DATA INFILE '/var/lib/mysql-files/departments.txt' INTO TABLE departments;mysql> LOAD DATA INFILE '/var/lib/mysql-files/dept_emp.txt' INTO TABLE dept_emp;mysql> LOAD DATA INFILE '/var/lib/mysql-files/dept_manager.txt' INTO TABLE dept_manager;mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.txt' INTO TABLE employees;mysql> LOAD DATA INFILE '/var/lib/mysql-files/salaries.txt' INTO TABLE salaries;mysql> LOAD DATA INFILE '/var/lib/mysql-files/titles.txt' INTO TABLE titles;mysql> SET foreign_key_checks = 1;
101101
Lab 4.12: Velocidade de Importação e Exportação
Teste a velocidade de exportação e importação com uma tabela maior.mysql> USE nerv;mysql> CREATE TABLE salaries_BIG AS SELECT * FROM employees.salaries;mysql> INSERT INTO salaries_BIG SELECT * FROM salaries_BIG;mysql> INSERT INTO salaries_BIG SELECT * FROM salaries_BIG;mysql> INSERT INTO salaries_BIG SELECT * FROM salaries_BIG;mysql> SELECT COUNT(*) FROM salaries_BIG;mysql> SELECT * FROM salaries_BIG INTO OUTFILE '/var/lib/mysql-files/BIG.txt';mysql> TRUNCATE TABLE salaries_BIG;mysql> LOAD DATA INFILE '/var/lib/mysql-files/BIG.txt' INTO TABLE salaries_BIG;
102102
Lab 4.13: MySQL Workbench
Instale o MySQL Workbench.# yum -y install mysql-workbench-community# mysql-workbench
103103
Lab 4.14: MySQL Workbench
104104
Lab 4.15: MySQL Workbench
105105
Lab 4.16: MySQL Workbench
106106
Lab 4.17: MySQL Workbench
107107
Lab 4.18: MySQL Workbench
108108
Lab 4.19: MySQL Workbench
109109
Lab 4.20: MySQL Workbench
110110
Lab 4.21: MySQL Workbench
111111
Lab 4.22: MySQL Workbench
112112
Lab 4.23: MySQL Workbench
113113
Lab 4.24: MySQL Workbench
114114
Lab 4.25: MySQL Workbench
115115
Lab 4.26: MySQL Workbench
116116
Lab 4.27: MySQL Workbench
117117
Lab 4.28: MySQL Workbench
118118
Lab 4.29: MySQL Workbench
119119
Lab 4.30: MySQL Workbench
120120
Lab 4.31: MySQL Workbench
121121
Lab 4.32: MySQL Workbench
122122
Lab 4.33: MySQL Workbench
123123
Lab 4.34: MySQL Workbench
124124
Lab 4.35: MySQL Workbench
125125
Lab 4.36: MySQL Workbench
126126
Lab 4.37: MySQL Variables: Conexões
Altere estas Variables pelo arquivo de parâmetros, e reinicie o MySQL.max_connections = 1000 # default = 151max_user_connections = 0thread_cache_size = 200 # default = 8 + (max_connections / 100)back_log = 100 # default = max_connections / 5slow_launch_time = 2 # Slow_launch_threads
max_connect_errors = 10 # FLUSH HOSTS
host_cache_size = -1 # default = 128 + ((max_connections – 500) / 20)skip_name_resolve = OFF
connect_timeout = 10net_read_timeout = 30net_write_timeout = 60net_retry_count = 10 # FLUSH HOSTS
net_buffer_length = 16384max_allowed_packet = 4194304
wait_timeout = 28800 # 28800 segundos = 8 horas.interactive_timeout = 28800lock_wait_timeout = 31536000 # 31536000 segundos = 1 ano.
Se o MySQL não iniciar:# grep ERR /var/log/mysqld.log
127127
MySQL Logs
128128
Lab 4.38: MySQL Variables: Logs
Altere estas Variables pelo arquivo de parâmetros, e reinicie o MySQL.log_output = FILElog_error_verbosity = 3log_error = /var/lib/mysql/mysql-error.log
general_log = OFFgeneral_log_file = /var/lib/mysql/mysql-general.log
slow_query_log = ONslow_query_log_file = /var/lib/mysql/mysql-slow.loglong_query_time = 3600min_examined_row_limit = 1000log_slow_admin_statements = ONlog_queries_not_using_indexes = ONlog_throttle_queries_not_using_indexes = 10
129129
Lab 4.39: Event Scheduler
Habilite o Event Schedulermysql> SELECT @@GLOBAL.event_scheduler;mysql> SHOW PROCESSLIST;mysql> SET GLOBAL event_scheduler = ON;mysql> SELECT @@GLOBAL.event_scheduler;mysql> SHOW PROCESSLIST;mysql> SELECT @@GLOBAL.event_scheduler;
mysql> USE nerv;mysql> SHOW EVENTS;
mysql> CREATE TABLE IF NOT EXISTS messages ( id INT PRIMARY KEY AUTO_INCREMENT, message VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL);
130130
Lab 4.40: Event Scheduler
Habilite o Event Schedulermysql> SHOW EVENTS;
mysql> CREATE EVENT IF NOT EXISTS test_event_01ON SCHEDULE AT CURRENT_TIMESTAMPDOINSERT INTO messages(message,created_at) VALUES ('Test MySQL Event 1',NOW());mysql> SELECT * FROM messages;
mysql> CREATE EVENT test_event_02ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTEON COMPLETION PRESERVEDOINSERT INTO messages(message,created_at) VALUES ('Test MySQL Event 2',NOW());mysql> SELECT * FROM messages;
mysql> CREATE EVENT test_event_03ON SCHEDULE EVERY 1 MINUTESTARTS CURRENT_TIMESTAMPENDS CURRENT_TIMESTAMP + INTERVAL 1 HOURDOINSERT INTO messages(message,created_at) VALUES ('Test MySQL Event 3',NOW());mysql> SELECT * FROM messages;
mysql> SHOW EVENTS;mysql> DROP EVENT test_event_02;mysql> DROP EVENT test_event_03;mysql> SHOW EVENTS;
131
Backup & Recovery
131
132132
Engines● MyISAM● InnoDB● Memory (MyISAM-like)● Archive (MyISAM-like)● CSV● Merge (MyISAM-like)● Federated● NDB (MyISAM-like)● Blackhole● Example● Aria● Percona XtraDB (InnoDB-like)● Percona XtraDB Cluster (InnoDB-like)● Percona TokuDB
133133
Lab 5.1: Backup & RecoveryNo Oracle VM VirtualBox, crie uma nova VM, com o nome “Restore”, importanto novamente o arquivo /root/Linux.ova.Verifique o IP da VM, e a acesse via ssh.# ip addr
Pelo nmtui, altere o hostname da VM para Recover.localdomain.
Instale o Repositório do MySQL, edite o arquivo /etc/yum.repos.d/mysql-community.repo, habilite o reposítório do MySQL 5.7, desabilite do 8.0, e instale o MySQL 5.7.# yum -y install wget# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm# yum -y install mysql80-community-release-el7-3.noarch.rpm# yum -y install mysql-server# systemctl enable mysqld# systemctl start mysqld# grep password /var/log/mysqld.log# /usr/bin/mysqladmin -u root -p password 'Nerv2019.'# firewall-cmd --zone=public --add-port=3306/tcp --permanent# firewall-cmd --reload# firewall-cmd --zone=public --permanent --list-ports
134134
Lab 5.2: Cold Backup
Execute um Cold Backup.# systemctl stop mysqld# mkdir /root/ColdBackup# cp -rfvp /var/lib/mysql/ /root/ColdBackup/# systemctl start mysqld
Na máquina de Restore, pare o MySQL.# systemctl stop mysqld# rm -rf /var/lib/mysql/*
Na máquina original, copie o Backup para a VM de Restore.# scp -Cr /root/ColdBackup/mysql/* root@192.168.0.99:/var/lib/mysql/
Na VM de Restore, inicie o MySQL.# chown -R mysql:mysql /var/lib/mysql# systemctl start mysqld# mysql -u root -pNerv2019.mysql> SHOW DATABASES;mysql> SELECT COUNT(*) FROM employees.salaries;
135135
Lab 5.3: Not-so-cold Backup MyISAM
Duplique as tabelas do banco employees no banco nerv.mysql> USE nerv;mysql> CREATE TABLE departments AS SELECT * FROM employees.departments;mysql> CREATE TABLE dept_emp AS SELECT * FROM employees.dept_emp;mysql> CREATE TABLE dept_manager AS SELECT * FROM employees.dept_manager;mysql> CREATE TABLE employees AS SELECT * FROM employees.employees;mysql> CREATE TABLE salaries AS SELECT * FROM employees.salaries;mysql> CREATE TABLE titles AS SELECT * FROM employees.titles;
Altere as tabelas para o Engine MyISAM.mysql> ALTER TABLE departments ENGINE=MyISAM;mysql> ALTER TABLE dept_emp ENGINE=MyISAM;mysql> ALTER TABLE dept_manager ENGINE=MyISAM;mysql> ALTER TABLE employees ENGINE=MyISAM;mysql> ALTER TABLE salaries ENGINE=MyISAM;mysql> ALTER TABLE titles ENGINE=MyISAM;
136136
Lab 5.4: Not-so-cold Backup MyISAM
Coloque as tabelas MyISAM em LOCK.mysql> SHOW OPEN TABLES FROM nerv;mysql> FLUSH TABLE departments, dept_emp, dept_manager, employees, salaries,titles WITH READ LOCK;mysql> SHOW OPEN TABLES FROM nerv;
Em outra sessão, confira o Lock.mysql> USE nerv;mysql> SELECT * FROM departments;mysql> DELETE FROM departments;^C
Em outra sessão, execute a cópia pelo sistema operacional.# mkdir /root/WarmBackup# cp -rfpv /var/lib/mysql/nerv /root/WarmBackup
Na sessão original, desbloquei as tabelas.mysql> SHOW OPEN TABLES FROM nerv;mysql> UNLOCK TABLES;mysql> SHOW OPEN TABLES FROM nerv;
Na máquina original, copie o Backup para a máquina de Restore.# scp -Cr /root/WarmBackup/nerv/* root@192.168.0.99:/var/lib/mysql/nerv/
Na máquina de Restore, corrija as permissões e confira o banco restaurado.# chown -R mysql:mysql /var/lib/mysql/nervmysql> SELECT COUNT(*) FROM nerv.salaries;
137137
Lab 5.5: mysqldump
Execute um backup via mysqldump.# mkdir /root/Dump# mysqldump nerv > /root/Dump/nerv.sqlO que aconteceu?# mysqldump --all_databases > /root/Dump/nerv01.sql
Edite o arquivo gerado.O que ele contém?Quais suas desvantagens?
–-single-transaction--lock-all-tables
--events--routines
--compress--quick--no-autocommit
Execute novamente o Dump.# mysqldump --single-transaction --events --routines --compress --quick --no-autocommit nerv > /root/Dump/nerv.sql# mysqldump --single-transaction --events --routines --compress --quick --no-autocommit --all-databases > /root/Dump/nerv01.sql
138138
Lab 5.6: mysqldump
Copie os Dumps para a VM de Restore.# scp /root/Dump/nerv.sql root@192.168.0.99:/root/# scp /root/Dump/nerv01.sql root@192.168.0.99:/root/
Na VM de Restore, restaure os Dumps e confira o Restore.# mysql -u root -pNerv2019. nerv < /root/nerv.sqlmysql> SELECT COUNT(*) FROM nerv.salaries;# mysql -u root -pNerv2019. < /root/nerv01.sqlmysql> SELECT COUNT(*) FROM nerv.salaries;
O que acontece se o banco de dados não existir na VM de Restore, no 1o Dump? E no 2o?O que acontece se uma tabela existir na VM de Restore, mas não existir no 1o Dump? E no 2o?--add-drop-database
139139
Lab 5.7: mydumper / myloader
Copie os Dumps para a VM de Restore.# wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm# yum -y install mydumper-0.9.5-2.el7.x86_64.rpm# top -H# mydumper --routines --triggers --events --compress –threads=8# mydumper --routines --triggers --events --compress --threads=8 --database=nerv# ls -lh export*
Copie os Dumps para a VM de Restore.# scp -Cr export-* root@192.168.0.99:/root/
Na VM de Restore, restaure os Dumps (o completo e o de um só banco) e confira o Restore.# wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm# yum -y install mydumper-0.9.5-2.el7.x86_64.rpm# myloader --user=root -–password=Nerv2019. --directory=/root/export-20191203-203701/ --overwrite-tables --threads=8# myloader --user=root -–password=Nerv2019. --directory=/root/eexport-20191203-203826/ --overwrite-tables –-threads=8
140140
Lab 5.8: Bin Logs
Adicione no /etc/my.cnf as linhas abaixo, e reinicie 3 vezes o mysql.server_id = 1sync_binlog = 1log-bin = mysql-binexpire_logs_days = 32Que novos arquivos passaram a existir no diretório de dados?
Execute um Dump com a opção --master-data.# mysqldump --single-transaction --events --routines --compress --quick --no-autocommit –-master-data=2 --all-databases > /root/Dump/nerv01-BinLog.sqlQue nova informação existe no arquivo de Dump?
Execute alterações no banco de dados.mysql> SHOW BINARY LOGS;mysql> USE nerv;mysql> CREATE TABLE salaries_test AS SELECT * FROM salaries;mysql> TRUNCATE TABLE salaries;mysql> INSERT INTO salaries_test SELECT * FROM salaries_test;mysql> SHOW BINARY LOGS;mysql> FLUSH LOGS;mysql> SHOW BINARY LOGS;
Copie o Dump e os Bin Logs para a VM de Restore.# scp /root/Dump/nerv01-BinLog.sql root@192.168.0.99:/root/# scp /var/lib/mysql/mysql-bin.0* root@192.168.0.99:/root/
141141
Lab 5.9: Bin LogsNa VM de Restore, restaure o Dump, e os Bin Logs.# mysql -u root -pNerv2019. < /root/nerv01-BinLog.sql# head -100 /root/nerv01-BinLog.sql | grep MASTER_LOG_POS# mysqlbinlog --start-position=154 mysql-bin.000003 > Recover.sql# mysqlbinlog mysql-bin.000004 >> Recover.sql# mysqlbinlog mysql-bin.000005 >> Recover.sql# grep salaries Recover.sql# mysql -u root -pNerv2019. < Recover.sql
mysql> SELECT COUNT(*) FROM nerv.salaries;mysql> SELECT COUNT(*) FROM nerv.salaries_test;
142142
XtraBackup
143143
XtraBackup / MyQL Enterprise Backup / Mariabackup
Percona Percona XtraBackup 1.4Released on November 22, 2010https://www.percona.com/doc/percona-xtrabackup/2.4/release-notes/1.4.html
Changes in MySQL Enterprise Backup 3.12.0 (2015-03-16)https://dev.mysql.com/doc/relnotes/mysql-enterprise-backup/3.12/en/
Mariabackup was first released in MariaDB 10.1.23 and MariaDB 10.2.7.It was first released as GA in MariaDB 10.1.26 and MariaDB 10.2.10.https://mariadb.com/kb/en/library/mariabackup-overview/
MariaDB 10.1.23 Release NotesRelease date: 3 May 2017https://mariadb.com/kb/en/library/mariadb-10123-release-notes/
144144
XtraBackup
https://www.percona.com/doc/percona-xtrabackup/8.0/intro.html
145145
XtraBackup
https://www.percona.com/doc/percona-xtrabackup/8.0/intro.html
146146
XtraBackup
https://www.percona.com/doc/percona-xtrabackup/8.0/intro.html
147147
Lab 5.10: xtrabackup
Instale o xtrabackup.# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm# yum -y install epel-release-latest-7.noarch.rpm# wget https://www.percona.com/redir/downloads/percona-release/redhat/1.0-13/percona-release-1.0-13.noarch.rpm# yum -y install percona-release-1.0-13.noarch.rpm# yum -y install percona-xtrabackup-24.x86_64
Execute um backup.# mkdir -p /root/XtraBackup/# export HORADOBACKUP=`date +%Y%m%d-%H%M%S`# xtrabackup --backup --parallel=4 --target-dir=/root/XtraBackup-$HORADOBACKUP/# xtrabackup --prepare --target-dir=/root/XtraBackup-$HORADOBACKUP/# ls -lh /root/XtraBackup-$HORADOBACKUP/# cat /root/XtraBackup-$HORADOBACKUP/xtrabackup_binlog_info
Restaure o backup.# systemctl stop mysqld# rm -rf /var/lib/mysql/*# xtrabackup --copy-back --target-dir=/root/XtraBackup-$HORADOBACKUP/# chown -R mysql:mysql /var/lib/mysql# systemctl start mysqld
148148
Lab 5.11: xtrabackup
Na VM de Restore, pare o MySQL.# systemctl stop mysqld# rm -rf /var/lib/mysql/*
Copie o Backup para a VM de Restore.# scp -Cr /root/XtraBackup-$HORADOBACKUP/* root@192.168.0.99:/var/lib/mysql/
Na VM de Restore, inicie o MySQL e confira o Restore.# chown -R mysql:mysql /var/lib/mysql# systemctl start mysqldmysql> SELECT COUNT(*) FROM nerv.salaries;mysql> SELECT COUNT(*) FROM nerv.salaries_test;
149149
Lab 5.12: xtrabackup
Crie os diretórios para os backups incrementais.# mkdir /root/XtraBackup/base# mkdir /root/XtraBackup/incremental1# mkdir /root/XtraBackup/incremental2# mkdir /root/XtraBackup/incremental3
Execute um backup base, e os incrementais.# xtrabackup --backup --target-dir=/root/XtraBackup/base/mysql> CREATE TABLE nerv.AposBackupBase (c1 INT);
# xtrabackup --backup --target-dir=/root/XtraBackup/incremental1/ --incremental-basedir=/root/XtraBackup/base/mysql> CREATE TABLE nerv.AposBackupIncremental1 (c1 INT);
# xtrabackup --backup --target-dir=/root/XtraBackup/incremental2/ --incremental-basedir=/root/XtraBackup/incremental1/mysql> CREATE TABLE nerv.AposBackupIncremental2 (c1 INT);
# xtrabackup --backup --target-dir=/root/XtraBackup/incremental3/ --incremental-basedir=/root/XtraBackup/incremental2/mysql> CREATE TABLE nerv.AposBackupIncremental3 (c1 INT);
Verifique o tamanho dos backups.# du -sh /root/XtraBackup/base/# du -sh /root/XtraBackup/incremental*# ls -lh /root/XtraBackup/base/nerv/# ls -lh /root/XtraBackup/incremental1/nerv/
150150
Lab 5.13: xtrabackup
Atualize o backup base com os backups incrementais.# xtrabackup --prepare --apply-log-only --target-dir=/root/XtraBackup/base/# xtrabackup --prepare --apply-log-only --target-dir=/root/XtraBackup/base/ --incremental-dir=/root/XtraBackup/incremental1/# xtrabackup --prepare --apply-log-only --target-dir=/root/XtraBackup/base/ --incremental-dir=/root/XtraBackup/incremental2/# xtrabackup --prepare --apply-log-only --target-dir=/root/XtraBackup/base/ --incremental-dir=/root/XtraBackup/incremental3/
Restaure e confira o Restore.# systemctl stop mysqld# rm -rf /var/lib/mysql/*# xtrabackup --copy-back --target-dir=/root/XtraBackup/base/# chown -R mysql:mysql /var/lib/mysql/# systemctl start mysqldmysql> USE nerv;mysql> SHOW TABLES;
151151
Lab 5.14: xtrabackup / innobackupex
Crie um diretório e execute um backup comprimido.# mkdir -p /root/XtraBackup/compress/# innobackupex --no-timestamp --datadir=/var/lib/mysql/ --user=root --password=Nerv2019. --parallel=4 --compress --compress-threads=4 /root/XtraBackup/compress/# ls -lh /root/XtraBackup/compress/nerv/
Prepare o backup para restauração.# yum -y install qpress# innobackupex --decompress --parallel=4 /root/XtraBackup/compress/# ls -lh /root/XtraBackup/compress/nerv/# rm -rf /root/XtraBackup/compress/*.qp /root/XtraBackup/compress/*/*.qp# innobackupex --apply-log --parallel=4 --use-memory=1G /root/XtraBackup/compress/
Restaure e confira o Restore.# systemctl stop mysqld# rm -rf /var/lib/mysql/*# xtrabackup --copy-back --target-dir=/root/XtraBackup/compress/# chown -R mysql:mysql /var/lib/mysql/# systemctl start mysqldmysql> USE nerv;mysql> SHOW TABLES;
152152
Lab 5.15: xtrabackup / innobackupexCrie os diretórios, e execute backups comprimidos e incrementais.# mkdir -p /root/XtraBackup/compress/base# mkdir -p /root/XtraBackup/compress/incremental1# mkdir -p /root/XtraBackup/compress/incremental2# mkdir -p /root/XtraBackup/compress/incremental3
# innobackupex --no-timestamp --datadir=/var/lib/mysql/ --parallel=4 --compress --compress-threads=4 /root/XtraBackup/compress/basemysql> CREATE TABLE nerv.AposBackupCompressedBase (c1 INT);
# innobackupex --no-timestamp --parallel=4 --compress --compress-threads=4 --incremental /root/XtraBackup/compress/incremental1/ --incremental-basedir=/root/XtraBackup/compress/base/mysql> CREATE TABLE nerv.AposBackupCompressedIncremental1 (c1 INT);
# innobackupex --no-timestamp --parallel=4 --compress --compress-threads=4 --incremental /root/XtraBackup/compress/incremental2/ --incremental-basedir=/root/XtraBackup/compress/incremental1/mysql> CREATE TABLE nerv.AposBackupCompressedIncremental2 (c1 INT);
# innobackupex --no-timestamp --parallel=4 --compress --compress-threads=4 --incremental /root/XtraBackup/compress/incremental3/ --incremental-basedir=/root/XtraBackup/compress/incremental2/mysql> CREATE TABLE nerv.AposBackupCompressedIncremental3 (c1 INT);
153153
Lab 5.16: xtrabackup / innobackupexAtualize o backup base com os backups incrementais.# innobackupex --decompress --parallel=4 /root/XtraBackup/compress/base/# innobackupex --apply-log --redo-only --use-memory=1G /root/XtraBackup/compress/base/
# innobackupex --decompress --parallel=4 /root/XtraBackup/compress/incremental1/# innobackupex --apply-log --redo-only --use-memory=1G /root/XtraBackup/compress/base/ --incremental-dir=/root/XtraBackup/compress/incremental1/
# innobackupex --decompress --parallel=4 /root/XtraBackup/compress/incremental2/# innobackupex --apply-log --redo-only --use-memory=1G /root/XtraBackup/compress/base/ --incremental-dir=/root/XtraBackup/compress/incremental2/
# innobackupex --decompress --parallel=4 /root/XtraBackup/compress/incremental3/# innobackupex --apply-log --redo-only --use-memory=1G /root/XtraBackup/compress/base/ --incremental-dir=/root/XtraBackup/compress/incremental3/
# rm -rf /root/XtraBackup/compress/base/*.qp
Restaure e confira o Restore.# systemctl stop mysqld# rm -rf /var/lib/mysql/*# xtrabackup --copy-back --target-dir=/root/XtraBackup/compress/base/# chown -R mysql:mysql /var/lib/mysql# systemctl start mysqldmysql> USE nerv;mysql> SHOW TABLES;
154
Tuning
154
155155
Lab 6.1: datadir
Altere a localização do diretório de dados do MySQL.# systemctl stop mysqld# mkdir /mysql# cp -R -p -v /var/lib/mysql/* /mysql/
# chown -R mysql:mysql /mysql# yum -y install policycoreutils-python# semanage fcontext -a -t mysqld_db_t "/mysql(/.*)?"# restorecon -Rv /mysql# cat /etc/selinux/targeted/contexts/files/file_contexts.local# chcon -R -u system_u -r object_r -t mysqld_db_t /mysql
# vi /etc/my.cnf...datadir=/mysql...
# systemctl start mysqld
156156
Lab 6.2: InnoDB System Tablespace
Altere a localização da System Tablespace do InnoDB do MySQL.# systemctl stop mysqld# mkdir /InnoDB01# mv /mysql/ibdata1 /InnoDB01/
# chown -R mysql:mysql /InnoDB01# semanage fcontext -a -t mysqld_db_t "/InnoDB01(/.*)?"# restorecon -Rv /InnoDB01# cat /etc/selinux/targeted/contexts/files/file_contexts.local# chcon -R -u system_u -r object_r -t mysqld_db_t /InnoDB01
# vi /etc/my.cnf...innodb_file_per_table=OFFinnodb_data_home_dir=innodb_data_file_path=/InnoDB01/ibdata1:10M:autoextend:max:10G...
# systemctl start mysqld
157157
Lab 6.3: Bin Logs
Altere a localização dos Bin Logs do MySQL.# systemctl stop mysqld# mkdir /BinLogs# mv /mysql/mysql-bin* /BinLogs/
# chown -R mysql:mysql /BinLogs# semanage fcontext -a -t mysqld_db_t "/BinLogs(/.*)?"# restorecon -Rv /BinLogs# cat /etc/selinux/targeted/contexts/files/file_contexts.local# chcon -R -u system_u -r object_r -t mysqld_db_t /BinLogs
# vi /etc/my.cnf...log-bin=/BinLogs/mysql-bin...
# systemctl start mysqld# systemctl restart mysqld# systemctl restart mysqld# ls -lh /BinLogs/
158158
Lab 6.4: InnoDB Logs
Altere a localização dos Logs do InnoDB.# systemctl stop mysqld# mkdir /InnoDBLogs# mv /mysql/ib_logfile* /InnoDBLogs/
# chown -R mysql:mysql /InnoDBLogs# semanage fcontext -a -t mysqld_db_t "/InnoDBLogs(/.*)?"# restorecon -Rv /InnoDBLogs# cat /etc/selinux/targeted/contexts/files/file_contexts.local# chcon -R -u system_u -r object_r -t mysqld_db_t /InnoDBLogs
# vi /etc/my.cnf...innodb_log_group_home_dir=/InnoDBLogs/...
# systemctl start mysqld# ls -lh /InnoDBLogs/
159159
Lab 6.5: Tablespaces InnoDB
Crie uma TABLESPACE, e mova tabelas para ela.# mkdir /BigTablespace
# chown -R mysql:mysql /BigTablespace# semanage fcontext -a -t mysqld_db_t "/BigTablespace(/.*)?"# restorecon -Rv /BigTablespace# cat /etc/selinux/targeted/contexts/files/file_contexts.local# chcon -R -u system_u -r object_r -t mysqld_db_t /BigTablespace
mysql> CREATE TABLESPACE BigTS ADD DATAFILE '/BigTablespace/BigTS.ibd' Engine=InnoDB;# ls -lh /mysql/nerv/salaries*# ls -lh /BigTablespace/BigTS.ibd
mysql> ALTER TABLE nerv.salaries_BIG TABLESPACE BigTS;mysql> ALTER TABLE nerv.salaries_test TABLESPACE BigTS;# ls -lh /mysql/nerv/salaries*# ls -lh /BigTablespace/BigTS.ibd
160160
Engines: InnoDB
161161
Engines: MyISAM
162162
Engines: Memory
163163
Engines: Archive
164164
Lab 6.6: myisampack Crie uma tabela graande, e teste sua velocidade de leitura antes e depois da compactação.mysql> CREATE TABLE nerv.salaries_BIG_RO AS SELECT * FROM nerv.salaries_BIG;mysql> ALTER TABLE nerv.salaries_BIG_RO Engine=MyISAM;mysql> INSERT INTO nerv.salaries_BIG_RO SELECT * FROM nerv.salaries_BIG_RO;mysql> INSERT INTO nerv.salaries_BIG_RO SELECT * FROM nerv.salaries_BIG_RO;mysql> INSERT INTO nerv.salaries_BIG_RO SELECT * FROM nerv.salaries_BIG_RO;mysql> CREATE INDEX IX_salaries_BIG_RO_emp_no ON nerv.salaries_BIG_RO(emp_no);
mysql> SELECT COUNT(salary) FROM nerv.salaries_BIG_RO;mysql> SELECT SUM(salary) FROM nerv.salaries_BIG_RO;mysql> SELECT SUM(salary) FROM nerv.salaries_BIG_RO;mysql> SELECT SUM(salary) FROM nerv.salaries_BIG_RO;
# myisamchk -dvv /mysql/nerv/salaries_BIG_RO# ls -lh /mysql/nerv/salaries_BIG_RO*# myisampack -v /mysql/nerv/salaries_BIG_RO# ls -lh /mysql/nerv/salaries_BIG_RO*# myisamchk -rq /mysql/nerv/salaries_BIG_RO# ls -lh /mysql/nerv/salaries_BIG_RO*# myisamchk -dvv /mysql/nerv/salaries_BIG_ROmysql> SELECT COUNT(salary) FROM nerv.salaries_BIG_RO;mysql> SELECT SUM(salary) FROM nerv.salaries_BIG_RO;O que aconteceu?mysql> FLUSH TABLES;
# myisamchk --unpack /mysql/nerv/salaries_BIG_RO# ls -lh /mysql/nerv/salaries_BIG_RO*
165165
Lab 6.7: Benchmark - mysqlslap
Execute um teste de carga no MySQL.# yum -y install mysql-community-test# mysqlslap --user=root –-password=Nerv2019. --auto-generate-sql --concurrency=10 --iterations=10 --number-char-cols=10 --number-int-cols=5 --engine=innodb
Durante a execução do teste, acompanhe no Linux, via top.# top -H
Durante a execução do teste, acompanhe no MySQL, via PROCESSLIST.mysql> SHOW PROCESSLIST;
Execute novamente o teste, mas com os Engines MyISAM e Memory.Qual o Engine mais rápido?Qual o Engine mais escalável?
166166
Lab 6.8: Benchmark - sysbench
Instale o sysbench.# yum -y install sysbench
Durante a execução do teste, acompanhe no MySQL, via mysqladmin.mysql> create database sbtest;mysql> create user 'sbtest'@'localhost' identified by 'Nerv2019.';mysql> grant all privileges on sbtest.* to 'sbtest'@'localhost';
# sysbench --db-driver=mysql /usr/share/sysbench/oltp_read_only.lua --threads=4 --mysql-host=localhost --mysql-user=sbtest --mysql-password=Nerv2019. --mysql-port=3306 --tables=10 --table-size=1000000 prepare
# sysbench --db-driver=mysql /usr/share/sysbench/oltp_read_only.lua --threads=16 --events=0 --time=300 --mysql-host=localhost --mysql-user=sbtest --mysql-password=Nerv2019. --mysql-port=3306 --tables=10 --table-size=1000000 --report-interval=1 run
Durante a execução do teste, acompanhe no MySQL, via PROCESSLIST.
167167
Lab 6.9: Benchmark - sysbench
Durante a execução do teste, acompanhe no MySQL, via mysqladmin.mysql> drop database sbtest;mysql> create database sbtest;mysql> grant all privileges on sbtest.* to 'sbtest'@'localhost';
# sysbench --db-driver=mysql /usr/share/sysbench/tpcc.lua --tables=10 --scale=1 --threads=4 --mysql-host=localhost --mysql-user=sbtest --mysql-password=Nerv2019. --mysql-port=3306 prepare
# sysbench --db-driver=mysql /usr/share/sysbench/tpcc.lua --tables=10 --scale=1 --threads=16 --mysql-host=localhost --mysql-user=sbtest --mysql-password=Nerv2019. --mysql-port=3306 run
Durante a execução do teste, acompanhe no MySQL, via PROCESSLIST.
168168
Lab 6.10: mytop
Instale o mytop.# yum -y install mytop# mytop -d mysql
Execute novamente os testes de carga, e acompanhe pelo mytop.
169169
Lab 6.11: innotop
Instale o innotop.# yum -y install innotop# innotop
Execute novamente os testes de carga com InnoDB, e acompanhe pelo innotop.
170170
Lab 6.12: SHOW ENGINE INNODB
Execute novamente os teste de carga com InnoDB, e acompanhe pelo comando abaixo.# mysql -E -e "SHOW ENGINE INNODB STATUS"
171171
Engines: CSV
172172
Engines: Merge
173173
Engines: Federated
174174
Engines: Federated - Connection
175175
Engines: Federated - Server
176176
Engines: Blackhole
177177
Engines: Example
178178
Lab 6.13: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### Huge Pageslarge_pages = OFF
### Asyncronous Writeflush = OFFflush_time = 0
### Temporary Tablesmax_heap_table_size = 16777216default_tmp_storage_engine = MyISAMtmp_table_size = 8M # (Created_tmp_tables – Created_tmp_disk_tables) > 0tmpdir = /tmp/
### Query Cachequery_cache_type = ON # (BI / OLAP / DSS)query_cache_size = 16M # (Qcache_hits)query_cache_limit = 1M # (Qcache_inserts)query_cache_min_res_unit = 4096 # (Qcache_inserts)
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.htmlhttps://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
179179
Lab 6.14: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### Metadataopen_files_limit = 10000table_open_cache = 400 # (Opened_tables)table_open_cache_instances = 1 # Thread State Opening tablestable_definition_cache = 600 # (400 + (table_open_cache / 2))metadata_locks_cache_size = 1024 # (Waiting for table metadata lock)metadata_locks_hash_instances = 8 # (Waiting for table metadata lock)max_prepared_stmt_count = 16382 # (Com_prepare_sql Prepared_stmt_count)query_prealloc_size = 8192query_alloc_block_size = 8192stored_program_cache = 256transaction_alloc_block_size = 8192transaction_prealloc_size = 4096
### Individual Buffersread_buffer_size = 2M # Thread State Sending dataread_rnd_buffer_size = 2Msort_buffer_size = 2M # (Sort_merge_passes)max_length_for_sort_data = 8388608 join_buffer_size = 2M # (index scans, range index scans, joins FTS)max_join_size = 4Grange_alloc_block_size = 4096
180180
Lab 6.15: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### MyISAMkey_cache_block_size = 4096key_buffer_size = 128M # 25% RAMkey_cache_age_threshold = 300key_cache_division_limit = 100 # (Hot)bulk_insert_buffer_size = 8388608concurrent_insert = ALWAYSdelay_key_write = ALLlow_priority_updates = OFF # (INSERT, UPDATE, DELETE, LOCK TABLE WRITE)myisam_sort_buffer_size = 8388608 # (REPAIR TABLE, CREATE INDEX, ALTER TABLE)myisam_max_sort_file_size = 2147483648 # (REPAIR TABLE,ALTER TABLE, LOAD DATA INFILE)
Exemplo:prod.key_buffer_size = 128Mdev.key_buffer_size = 16Mmysql> CACHE INDEX t1, t2, t3 IN prod;
181181
Lab 6.16: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### InnoDB Filesinnodb_checksums = ON # < 5.7innodb_checksum_algorithm = crc32innodb_doublewrite = OFFinnodb_autoextend_increment = 64innodb_file_per_table = 1innodb_file_format = Barracudainnodb_compression_level = 6innodb_compression_failure_threshold_pct = 0innodb_compression_pad_pct_max = 50
### InnoDB Threadsinnodb_numa_interleave = OFFinnodb_read_io_threads = 4 # (Pending reads) show engine innodb status;innodb_write_io_threads = 4 # (Pending writes)innodb_thread_concurrency = 0 # (cs)innodb_concurrency_tickets = 5000innodb_thread_sleep_delay = 10000innodb_adaptive_max_sleep_delay = 150000
### Lockinnodb_lock_wait_timeout = 50innodb_rollback_on_timeout = OFF
182182
Lab 6.17: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### InnoDB Bufferinnodb_page_size = 16384innodb_buffer_pool_size = 2Ginnodb_buffer_pool_instances = 1 # 1 para cada 10GB de innodb_buffer_pool_sizeinnodb_change_buffering = ALLinnodb_change_buffer_max_size = 25
innodb_buffer_pool_dump_at_shutdown = ONinnodb_buffer_pool_load_at_startup = ONinnodb_buffer_pool_dump_now = OFFinnodb_buffer_pool_load_now = OFFinnodb_buffer_pool_load_abort = OFFinnodb_buffer_pool_filename = InnoDB.dumpinnodb_buffer_pool_dump_pct = 100
innodb_random_read_ahead = ON # (OFF em SSD)innodb_read_ahead_threshold = 32
### InnoDB Undoinnodb_purge_threads = 1 ### show engine innodb status; (History list length)innodb_purge_batch_size = 300innodb_max_purge_lag = 0 # (History list length)innodb_max_purge_lag_delay = 0 # (History list length)
183183
Lab 6.18: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### InnoDB Flushinnodb_use_native_aio = ON # Linuxinnodb_flush_method = O_DIRECTinnodb_flush_neighbors = 1 # (0 em SSD)innodb_adaptive_flushing = ONinnodb_flushing_avg_loops = 30innodb_adaptive_flushing_lwm = 10innodb_lru_scan_depth = 1024innodb_max_dirty_pages_pct_lwm = 10innodb_max_dirty_pages_pct = 75innodb_old_blocks_pct = 37innodb_old_blocks_time = 1000innodb_io_capacity = 200innodb_io_capacity_max = 2000
### InnoDB Loginnodb_log_file_size = 32Minnodb_log_files_in_group = 7innodb_log_buffer_size = 64Minnodb_log_compressed_pages = ONinnodb_flush_log_at_trx_commit = 1innodb_flush_log_at_timeout = 1 # (1 a 2700)innodb_fast_shutdown = 1
184184
Lab 6.19: Parâmetros de TuningAltere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### InnoDB Statisticsinnodb_stats_auto_recalc = ONinnodb_stats_transient_sample_pages = 8 # (STATS_SAMPLE_PAGES)innodb_stats_persistent = ONinnodb_stats_persistent_sample_pages = 20 # (STATS_SAMPLE_PAGES)
### InnoDB Optimizerinnodb_adaptive_hash_index = ONinnodb_sort_buffer_size = 67108864 # (CREATE INDEX)innodb_disable_sort_file_cache = OFF
185185
Parâmetros de Tuning - Optimizer
186186
Lab 6.20: Parâmetros de Tuning
Altere os parâmetros abaixo de forma a otimizar os Labs 4.12, 5.6, 6.6, 6.7 e 6.9.### Optimizeroptimizer_prune_level = 1optimizer_search_depth = 62eq_range_index_dive_limit = 10max_seeks_for_key = 1000
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
187187
Lab 6.21: Transações e Isolamento
Altere todos os parâmetros abaixo no arquivo de parâmetros, e reinicie o MySQL.mysql> use nerv;mysql> CREATE TABLE teste1 (c1 int, c2 char(50), c3 varchar(255)) Engine=InnoDB;mysql> CREATE TABLE teste2 (c1 int, c2 char(50), c3 varchar(255)) Engine=MyISAM;
mysql> insert into teste1 values (1, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (2, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (3, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (4, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (5, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (6, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (7, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (8, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (9, 'AAA', 'AAAAAAAAAA');mysql> insert into teste1 values (10, 'AAA', 'AAAAAAAAAA');mysql> insert into teste2 select * from teste1;
188188
Lab 6.22: Transações e Isolamento1a Sessão:mysql> update teste1 set c2 = 'BBB';mysql> select * from teste1;
mysql> start transaction;mysql> update teste1 set c2 = 'CCC';mysql> select * from teste1;
mysql> commit;mysql> select * from teste1;
mysql> start transaction;mysql> update teste1 set c2 = 'DDD';
mysql> commit;mysql> select * from teste1;
2a Sessão:
mysql> select * from teste1;
mysql> select * from teste1;
mysql> select * from teste1;
mysql> update teste1 set c2 = 'EEE';
mysql> select * from teste1;
3a Sessão:mysql> SHOW PROCESSLIST;mysql> SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits;mysql> SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 3;
189189
Lab 6.23: Transações e Isolamento1a Sessão:mysql> update teste2 set c2 = 'BBB';mysql> select * from teste2;
mysql> start transaction;mysql> update teste2 set c2 = 'CCC';mysql> select * from teste2;
mysql> commit;mysql> select * from teste2;
mysql> start transaction;mysql> update teste2 set c2 = 'DDD';
mysql> commit;mysql> select * from teste2;
2a Sessão:
mysql> select * from teste2;
mysql> select * from teste2;
mysql> select * from teste2;
mysql> update teste2 set c2 = 'EEE';
mysql> select * from teste2;
190190
Lab 6.24: Comandos AdministrativosExecute estas operações no banco employees e nerv:mysql> ANALYZE TABLE employees.salaries;mysql> INSERT INTO nerv.salaries SELECT * FROM employees.salaries;mysql> ANALYZE TABLE nerv.salaries;
mysql> OPTIMIZE TABLE employees.salaries;
# ls -lh /mysql/nerv/salaries.*mysql> OPTIMIZE TABLE nerv.salaries;# ls -lh /mysql/nerv/salaries.*mysql> DELETE FROM nerv.salaries;# ls -lh /mysql/nerv/salaries.*mysql> OPTIMIZE TABLE nerv.salaries;mysql> OPTIMIZE TABLE nerv.salaries;# ls -lh /mysql/nerv/salaries.*
mysql> REPAIR TABLE employees.salaries;mysql> REPAIR TABLE nerv.salaries;
# mysqlcheck -u root -pNerv2019. nerv# mysqlcheck -a -u root -pNerv2019. nerv# mysqlcheck -o -u root -pNerv2019. nerv# mysqlcheck -repair -u root -pNerv2019. nerv
# mysqlcheck -u root -pNerv2019. employees# mysqlcheck -a -u root -pNerv2019. employees# mysqlcheck -o -u root -pNerv2019. employees# mysqlcheck -repair -u root -pNerv2019. employees
191191
Lab 6.25: mysqldumpslowExecute o mysqldumpslow.# mysqldumpslow /var/lib/mysql/mysql-slow.log# mysqldumpslow /var/lib/mysql/mysql-slow.log > /root/Slow.txt# mysqldumpslow -s r /var/lib/mysql/mysql-slow.log > /root/Slow.txt
192192
Lab 6.26: EXPLAIN
mysql> use sakila;mysql> EXPLAIN SELECT actor.first_name, actor.last_name, film.titleFROM actor, film, film_actor WHERE actor.first_name = 'ADAM' AND actor.actor_id = film_actor.actor_id AND film_actor.film_id AND film.film_id = film_actor.film_id;
193193
Lab 6.27: SHOW PROFILE
194194
Partitioning
195195
Partitioning - RANGE
196196
Partitioning - RANGE
197197
Partitioning - LIST
198198
Partitioning - RANGE COLUMNS
199199
Partitioning - LIST COLUMNS
200200
Partitioning - HASH
201201
Partitioning - KEY
202202
memcached
https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-intro.html
203203
memcached
204
Alta Disponibilidade
204
205205
Alta Disponibilidade
DRBD / Pacemaker / Corosync
206206
Alta Disponibilidade
Windows Server Failover Clustering
207207
Alta Disponibilidade
MySQL Replication
208208
Lab 7.1: MySQL ReplicationNo Oracle VM VirtualBox, crie uma nova VM, com o nome “Slave 01”, importanto novamente o arquivo /root/Linux.ova.Inicie a VM, verifique seu IP, e acesse por ssh.Pelo nmtui, altere o hostname da VM.
Adicione a linha abaixo no arquivo de configuração, e reinicie o MySQL.server-id=2
Execute a configuração da Replicação no Master.mysql> CREATE USER 'rep'@'192.168.0.99' IDENTIFIED BY 'Nerv2019.';mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.0.99';mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;Backup no Mastermysql> UNLOCK TABLES;Restore no Slave
Execute a configuração da Replicação no Slave.mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.101',MASTER_USER='rep',MASTER_PASSWORD='Nerv2019.',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=435;
mysql> START SLAVE;mysql> SHOW SLAVE STATUS;# mysql -E -e "SHOW SLAVE STATUS" | grep -e Running -e Seconds
209209
GTID
210210
MySQL Parallel ReplicationAltere os parâmetros abaixo:slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 20
211211
MySQL Proxy
212212
Alta Disponibilidade
ProxySQL
https://www.proxysql.com/compare
213213
Proxy SQL# wget http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/7/proxysql-2.0.8-1-centos7.x86_64.rpm# yum -y install proxysql-2.0.8-1-centos7.x86_64.rpm
# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm# yum -y install mysql80-community-release-el7-3.noarch.rpm# vi /etc/yum.repos.d/mysql-community.repo
# yum -y install mysql-community-client proxysql
# systemctl enable proxysql# systemctl start proxysql
# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';Admin> UPDATE global_variables SET variable_value='Nerv2019.' WHERE variable_name='mysql-monitor_password';Admin> LOAD MYSQL VARIABLES TO RUNTIME;Admin> SAVE MYSQL VARIABLES TO DISK;
Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '192.168.0.97', 3306);Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.0.98', 3306);Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.0.99', 3306);Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers ORDER BY hostgroup_id;Admin> LOAD MYSQL SERVERS TO RUNTIME;Admin> SAVE MYSQL SERVERS TO DISK;
Admin> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sbtest', 'Nerv2019.', 1);Admin> LOAD MYSQL USERS TO RUNTIME;Admin> SAVE MYSQL USERS TO DISK;
214214
Proxy SQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES(1,1,'^SELECT.*FOR UPDATE$',1,1),(2,1,'^SELECT',2,1);Admin> LOAD MYSQL QUERY RULES TO RUNTIME;Admin> SAVE MYSQL QUERY RULES TO DISK;
Admin> INSERT INTO mysql_users (username, password) VALUES ('ricardo','Nerv2019.');Admin> LOAD MYSQL USERS TO RUNTIME;Admin> SAVE MYSQL USERS TO DISK;
mysql> CREATE USER 'ricardo'@'192.168.0.19' IDENTIFIED BY 'Nerv2019.';mysql> GRANT ALL ON *.* TO 'ricardo'@'192.168.0.19';mysql> CREATE USER 'monitor'@'192.168.0.19' IDENTIFIED BY 'Nerv2019.';mysql> GRANT ALL ON *.* TO 'monitor'@'192.168.0.19';mysql> FLUSH PRIVILEGES;
# mysql -u ricardo -pNerv2019. -h 127.0.0.1 -P 6033
215215
Alta Disponibilidade
MySQL Cluster / NDB Cluster
216216
Alta Disponibilidade
InnoDB Cluster / Group Replication / Router / Shell / Fabric
217217
MySQL Group Replication
218218
MySQL Labs
219219
Alta Disponibilidade
Galera Cluster
220220
Alta Disponibilidade
Galera Cluster
221221
Alta Disponibilidade
Percona XtraDB Cluster
222
Upgrade para 8.0
222
223223
Upgrade
Supported Upgrade MethodsIn-place Upgrade (mysql_upgrade)Logical Upgrade (mysqldump)
Supported Upgrade Paths● Para Release Series Version superior.Por exemplo, de 5.6.20 para 5.6.27.
● Para um (apenas um) Release Series superior.Por exemplo, de 5.6.27 para 5.7.9 (antes faça upgrade da Release Series Version).
Exemplos:5.6: Release Series5.6.20: Release Series Version
224224
Upgrade
Changes in MySQL 5.7 / Changes in MySQL 8.0https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.htmlhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
Server and Status Variables and Options Added, Deprecated, or Removedhttps://dev.mysql.com/doc/refman/5.7/en/added-deprecated-removed.htmlhttps://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html
Upgrading a Replication Setuphttps://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.htmlhttps://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html
Índice de alterações:https://dev.mysql.com/doc/relnotes/mysql/5.6/en/ix01.htmlhttps://dev.mysql.com/doc/relnotes/mysql/5.7/en/ix01.htmlhttps://dev.mysql.com/doc/relnotes/mysql/8.0/en/ix01.html
225225
Upgrade
226226
Upgrade
227227
8.0 New Features
http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release-is-available/https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
228228
Lab 9.1: Upgrade para 8.0
Edite o arquivo /etc/yum.repos.d/mysql-community.repo, e habilite o reposítório do MySQL 8.0....[mysql57-community]name=MySQL 5.7 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/enabled=0gpgcheck=1...[mysql80-community]name=MySQL 8.0 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/6/$basearch/enabled=1gpgcheck=1...
Execute o Upgrade para MySQL 8.0.# mysql --version# mysqldump --all-databases > backup.sql# ls -lh backup.sql# systemctl stop mysqld# yum -y update mysql-community-*# systemctl start mysqld# mysqlcheck --all-databases --check-upgrade# mysql_upgrade# grep upgrade /var/lib/mysql/mysql-error.log# mysql --version
229
Perguntas?http://nervinformatica.com.br/forum/
Ricardo Portilho Proni ricardo@nervinformatica.com.br Nerv Informática Ltda. Esta obra está licenciada sob a licença Creative Commons Atribuição-SemDerivados 3.0 Brasil. Para ver uma cópia desta licença, visite http://creativecommons.org/licenses/by-nd/3.0/br/.
230230
Lab Extra 1.1: Banco de Exemplo WORLD# cd# wget http://downloads.mysql.com/docs/world.sql.zip# ls -lh world.sql.zip# yum -y install unzip# unzip world.sql.zip# ls -lh world.sql# mysql < world.sql
mysql> SHOW DATABASES;mysql> use world;mysql> show tables;mysql> select count(*) from city;mysql> select count(*) from country;mysql> select count(*) from countrylanguage;
231231
Lab Extra 1.2: Banco de Exemplo SAKILA# cd# wget http://downloads.mysql.com/docs/sakila-db.zip# ls -lh sakila-db.zip# unzip sakila-db.zip# ls -lh sakila-db/
# cd sakila-db/# mysql < sakila-schema.sql# mysql < sakila-data.sql
mysql> SHOW DATABASES;mysql> use sakila;mysql> show tables;mysql> select count(*) from actor;mysql> select count(*) from film;mysql> select count(*) from film_actor;
232232
Lab Extra 1.3: Banco de Exemplo MENAGERIE
# cd# wget http://downloads.mysql.com/docs/menagerie-db.zip# ls -lh menagerie-db.zip# unzip menagerie-db.zip# ls -lh menagerie-db/
# cd menagerie-db/# cat pet.txt# cat event.txt# mysql -tmysql> CREATE DATABASE menagerie;mysql> use menagerie;mysql> SOURCE cr_pet_tbl.sqlmysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;mysql> SOURCE ins_puff_rec.sqlmysql> SOURCE cr_event_tbl.sqlmysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
mysql> show tables;mysql> select count(*) from pet;mysql> select count(*) from event;
233233
Lab Extra 1.4: Banco de Exemplo EMPLOYEES# cd# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2# ls -lh employees_db-full-1.0.6.tar.bz2# bunzip2 employees_db-full-1.0.6.tar.bz2# tar xfv employees_db-full-1.0.6.tar# ls -lh employees_db/
# cd employees_db/
# vi employees.sql...-- set storage_engine = InnoDB;...-- select CONCAT('storage engine: ', @@storage_engine) as INFO;...
# mysql -t < employees.sqlmysql> SHOW DATABASES;mysql> use employees;mysql> show tables;mysql> select count(*) from departments;mysql> select count(*) from employees;mysql> select count(*) from salaries;
234234
Lab Extra 1.5: Banco de Exemplo# mysqlmysql> use employees;mysql> show tables;
# mysql employeesmysql> show tables;
# mysqlmysql> select count(*) from employees.salaries;mysql> select count(*) from menagerie.pet;
mysql> select count(*) from employees.salaries;mysql> select count(*) from EMPLOYEES.SALARIES;mysql> select count(*) from EMPLOYEES.salaries;mysql> select count(*) from employees.SALARIES;mysql> select count(*) from Employees.salaries;mysql> select count(*) from employees.Salaries;
235
Fim
Ricardo Portilho Proni ricardo@nervinformatica.com.br Nerv Informática Ltda. Esta obra está licenciada sob a licença Creative Commons Atribuição-SemDerivados 3.0 Brasil. Para ver uma cópia desta licença, visite http://creativecommons.org/licenses/by-nd/3.0/br/.
Recommended