Posts

MySQL - Backup/Restore single table using mysqldump

-
Verification before restore Check number of records SELECT COUNT(*) FROM zabgate.host_inventory; -- 5874 lines Backup table steps using mysqldump 1) Create temporary backup directory (e.g /var/backups/zabgate/mysqldump) and give permission to mysql (ignore if path exist) drwxr-xr-x 2 mysql mysql 47 Mar 27 04:39 mysqldump 2) Backup table using mysqldump Command : Single table : mysqldump -uitdba -p db_name table_name > path/table_name.sql Multiple table: mysqldump -u root -p dbname table1 table2 table3 > table.sql Execution : mysqldump -uitdba -p zabgate host_inventory > /var/backups/zabgate/mysqldump/host_inventory_190327.sql Restore table steps 1) Create temp schema/ db Mysql > create database temp_backup; 2) Move the sql file from mysqldump directory to restore schema and make sure it's executable under mysql mv /var/backups/zabgate/mysqldump/host_inventory_190327.sql /seamnt/prd101/mysql/temp_backup/ 3) Restore table C...
Post a Comment
Read more

MySQL Tablespace

-
MySQL Tablespace Theories - InnoDB Tablespace general knowledge InnoDB tablespaces store data, metadata, buffered data, and logs on the file system. Data tablespaces System tablespace Stores metadata and buffers in system tablespace. Data dictionary: Table, index, and column metadata Undo log: Transactional rollback information Change buffer : Changes to secondary index pages Double Write buffer : Ensures crash-safe writes Typically ibdata1, ibdata2 Check system tablespace : Example 1 : mysql> show variables where variable_name='innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ Indicates that one file called ibdata1, 12MB in size, and autoextending Example 2 : ibdata1:20M;/ext/ibdata2:10M:autoextend Indicates that there are...
Post a Comment
Read more

MySQL DB Availability

-
Starting/ Stopping MySQL Starting MySQL: Run the mysqld binary directly. Run mysqld_safe. Run mysql.server with a parameter. Includes copies such as /etc/init.d/mysql Example (upstart/SysV init): service mysql start Example (systemd): systemctl start mysql Use systemctl command for MySQL rpm installation for systemd distribution : systemctl start mysqld On Windows, start by mysqld.exe Stopping MySQL: Kill the mysqld binary with the SIGTERM signal (-15). Kill mysqld_safe first if it is running. Run mysql.server with the parameter value stop. service mysql stop systemctl stop mysql Use systemctl command for MySQL rpm installation for systemd distribution : systemctl stop mysqld Stopping MySQL with mysqladmin (client login path): mysqladmin shutdown Sometimes mysqladmin commands need root privileges to run, hence you can also use (shutdown with credential ) mysqladmin -u root -p -h dbhost -P 3306 shutdown (shutdown with admin login path) mysqladmin --log...
Post a Comment
Read more

MySQL Connection Layer

-
MySQL Connection Layer The connection layer maintains one thread per connection. This thread handles query execution. Before a connection can begin sending SQL queries, the connection is authenticated by verification of username, password, and client host. The connection layer accepts connections from applications over several communication protocols: • TCP/IP • UNIX sockets • Shared memory • Named pipes Local and Remote Communications Protocol: TCP/IP The suite of communication protocols used to connect hosts on the Internet – Uses IP addresses or DNS host names to identify hosts – Uses TCP port numbers to identify specific services on each host – MySQL default TCP port number: 3306 Example using hostname mysql --host=mysqlhost1 -uroot -p Example using IP address mysql -h 192.168.1.8 -P 3309 -uroot -p Local Communication Protocol in Linux: Socket Example using socket file /var/lib/mysql/.socket mysql -S /var/lib/mysql/.socket -ur...
Post a Comment
Read more

DB Memory (MySQL Disk and Memory Resources)

-
MySQL Memory / Buffer Pool Checking Server Memory root@okprd5328:/usr/sbin# free Use free -g for gb, free -k for kb, free -m for mb total(KB) used free shared buff/cache available Mem: 7492252 1368184 386208 472708 5737860 5287860 Swap: 3145724 122624 3023100 7GB RAM, 1GB Used, 3.8GB Free [root@col-lnx229 ~]# cat /proc/meminfo MemTotal: 8010088 kB MemFree: 201568 kB MemAvailable: 5665884 kB Buffers: 9624 kB Cached: 5735120 kB SwapCached: 31220 kB Active: 3957396 kB Inactive: 3518076 kB Active(anon): 1139516 kB Inactive(anon): 754600 kB Active(file): 2817880 kB Inactive(file): 2763476 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 2097148 kB SwapFree: 1658620 kB Dirty: 64 kB Writeback: 0 kB AnonPages: 1709480 kB Mapped: ...
Post a Comment
Read more