My Notes : Mysql Database Concepts And Operations

RDBMS : Relational Database Management System

MySQL – MariaDB – Percona Server

MySQL Structure :

  • Connectors : methods to connect to MySQL server
  • Management Unilities : Tools to manage the database, such as backup, replication and cluster
  • Connection Pool : manage and cache user connection informations, like username and password, privilegess, quota
  • SQL interface, parser, optimizer : manage and handle the SQL processing tasks
  • Cache & buffers : memory space to cache the data in the databases
  • Storage engines : manage the data processing storage mechanism. Different engines for different business type
  • Database files : including configuration, data files, log files which located on the filesystem to make up the MySQL database

MySQL Structure-Storage Structure :

  • Tablespace : Highest level object of the database
  • Segment : corresponding to each logical object in database, like table, index. consist of multiple extents
  • Extent : Connection of multiple succesive pages.
  • Page : Minimun unit when the database is assignning space

MySQL Structure-File Structure :

Parameter files : /etc/my.cnf /etc/my.cnf.d/

Data files : /var/lib/mysql show variables like “‘datadir’

Log files :BinlogRedo log files : “innodb_log_group_home_dir”

Error log files : “log_error”Query log file “slow_query_log_file”

Other Files : 

PID file : /var/lib/mysql/

Stocket file : /var/lib/mysql/mysql.sock

MySQL database high availability introduction

High Availability Architecture Benifit

Fault Tolerance

– Avoid single point failure on computing layer

– Avoid single point failure on data layer

Load Balance

– Separate database read and write request

– Separate databa business and maintence operation

MySQL replication

Working thoery : 

– Database changed data will be logged in binlog

– Master and slave instance keep synchronized with binlog replication

Advantage :

– Easy to configure

– Simple architecture with 2 nodes only

Disvantage :

– Asynchronized replication can not guarantee data consistency between master and slave

MySQL Cluster

Working thoery : 

– SQL node : Tun MySQL service and process. Access point of the database

– NDB node : store all data of the database. NDB storage engine will guarantee data redundancy and consistency

Advantage :

– High data availability, redundancy and consistency

– No sharing data architecture

– High scalability, support online node expansion

Disvantage :

– Configuration and troubleshooting is complex

– High memory required on NDB node

– High network requirement between the NDB nodes

Galera Cluster

Working thoery : 

– Database changed data will be logged in binlog

– Gelera Cluster will use wsrep process to synchronize any changed data across all the nodes in the same galera cluster with binlog

Advantage :

– High data availability, redundancy and consistency

– Support write on multiple node

– Automated dection on node gailure and recovery

Disvantage :

– Only support innodb storage engine

– Extra software is required

DRBD Storage Replication

Working thoery : 

– Database  files is stored on the block device of linux OS.

– DRBD can achieve data replication in linux block device layer, thus make database HA in OS device layer.

Advantage :

– DRBD is intergrated in the OS

– Simple architecture with 2 nodes only

– high data consistency

Disvantage :

– High IO impact on the database

– Database on slave node is not readable

MySQL database backup and recovery

Database Backup Parameters

All Database

mysqldump -uroot -p –all-databases > all.sql

Drop Database

mysqldump -uroot -p -all-databases –add-drop-database > all.sql

Add Drop Table

mysqldump -uroot -p –all-databases –skip-add-drop-table > all.sql

mysqldump -uroot -p -all-databases > all.sql

Add Lock Tables

mysqldump -uroot -p –all-database > all.sql

mysqldump -uroot -p –all-database –skip-add-locks > all.sql

Add Comment

mysqldump -uroot -p –all-database –skip-comments > all.sql

–compatible : Exported data will be compatible with orther databases or older version of MySQL.

Value support : ANSI, mysql323, mysql40, PostgreSQL, Oracle, MSSQL, DB2, MaxDB, No_key_options, no_tables_options, no_field_options

mysqldump -uroot -p -all-databases –compatible=ansi > all.sql

–compact : less output information. Remove the structure of annotations and head and tail

–comple-insert, -c : use the full insert statement containing the column name. increase insertion efficiency, but can be affected by max_allowed_packet parameters and result in insertion failure.

–database, -B : databases are exported.

mysqldump -uroot -p –database test mysql > test_mysql.sql


mysqldump -urot -p –all-databases –default-character-set=latin1 > all.sql

Importing multiple database data :

mysql -u root -p < “/tmp/all.sql”

Importing one database data :

mysql -u root -p test < “/tmp/test.sql”

Importing table data :

mysql -u root -p test < “/tmp/table.sql?

source /tmp/table.sql

Physical Backup (Flie of Physical data)

innobackupes –defaults-file=/etc/my.cnf –user=backup –password=backup –socket=/tmp/mysql.sock /export

User Xtrabackup Tools

Full Backup

Create user and give privileges :

– CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘backup’;


Similar Articles



Most Popular

Điều chỉnh hiệu suất cho NGINX

NGINX nổi tiếng là một bộ cân bằng tải hiệu suất cao , bộ đệm và máy chủ web , cung cấp năng lượng cho hơn 40% các trang web...

Cách sử dụng Screen Linux

Bạn đã bao giờ phải đối mặt với tình huống bạn thực hiện một tác vụ dài hạn trên một máy từ...

Chrome Development Tools – Tips And Tricks

Google Chrome là trình duyệt web phổ biến nhất được sử dụng hiện nay, trong năm 2019 nó chiếm tới 63% lượng...