DBA DIARY

Percona Xtrabackup Issues

2016-08-04T07:04:00.002-07:00

Xtrabackup is a very good third party opensource tool for mysql DBA. Although its very easy to configure and use. but at time due to lack of OS dependencies it may fail.

I came across few such OS related issues and thought to document so that people can get benefited.

Error #1

./innobackupex --socket=/tmp/mysql.sock --datadir=/software/mysql1/data/ /software/backup/
160804 17:55:30 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.
BEGIN failed--compilation aborted at - line 693.
160804 17:55:30 Connecting to MySQL server host: localhost, user: (null), password: not set, port: 0, socket: /tmp/mysql.sock
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).

Execute the following command

yum install perl-Digest-MD5

====================================================================

Error #2

160804 18:27:20 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql3.sock' (using password: NO).
Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.
160804 18:27:20 Connecting to MySQL server host: localhost, user: (null), password: not set, port: 0, socket: /tmp/mysql3.sock
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql3.sock' (111).
[root@localhost bin]#

Execute the following command

yum install perl-DBD-MySQL

=================================================================

After this xtrabackup should work absolutely fine

xtrabackup: Transaction log of lsn (1676847) to (1676847) was copied.
160804 18:34:58 completed OK!

MySQL Training at Hyderabad

2015-12-13T00:51:00.002-08:00

MySQL Training at Hyderabad

Online or In person

please contact

kuldeepsirohi@gmail.com

9963770862

MySQL DBA Interview Questions and Answers - Series 1

2012-12-10T01:50:00.000-08:00

1. Describe MySQL architecture.

Answer: MySQL has got the logical architecture as follows

A. Connection Manager
B. Query Optimizer
C. Pluggable Engines.

2. What are the major differences between MySQL 5.1 and 5.5?

Answer: The Major difference I know is that the default engine was myisam in 5.1 and innodb in 5.5

3. What are the different database engines available in MySQL? (explain)

Answer: Following are the highly used engines available in mysql

A. MyISAM
B. INNODB
C. Memory
D. Federated
E. CSV

4. What are the major differences between MyISAM and InnoDB?

Answer: Following are the differences between InnoDB and MyISAM

A. MyISAM does not support transactions whereas InnoDB does.
B. InnoDB supports multi versioning concurrency control.
C. MyISAM uses table level locks and InnoDB uses row level locking.

5. Which engine would you prefer for production OLTP environment and why?

Answer: InnoDB (to be transaction safe)

6. What are the best installation practices for MySQL?

Answer: there are following installation methods available

A. Binary Installation
B. RPM Installation
C. Source Code compilation

after installation you should change the location of bin logs and datafiles on the different physical disks.

7. Which RAID level is best suited for MySQL?

Answer: RAID 10

8. How do you upgrade from one mysql version to another mysql version?

Answer: create a slave on newer version and change it to MASTER.

9. How many types of logs are there in mysql?

Answer: General Log, Error Log, Binary Log and Slow Query Log

10. How do you find out slow queries in mysql?

Answer : By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1;

11. How do you go through the MySQL slow query log?

Answer : slow query log might be very huge in size and query could be listed thousand times. to summarize the slow query log in a very informative way there is third party tool available 'pt-query-digest' which is a part of percona tool kit freely downloadable.

12. How do you check the uptime of a mysql server?

Answer : following command gives you the system status

status

13. If the mysql server is performing slow than how to find out the process which is causing problem.

Answer : show processlist

14. What do you do if the end user complains about the performance of the DB?

Answer : show processlist will show the processes which taking resources at db server and the do the rest diagnosis.

15. What do you do about the slow queries?

Answer : study the explain plan and create necessary indexes if required.

16. Where do you change the performance parameters of mysql and by default where is the file located on a unix system?

Answer : my.cnf. this file is available under /etc/my.cnf

17. Which are the important performance parameters for MyISAM and InnoDB?

Answer : For MyISAM

key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache

For InnoDB

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size
table_open_cache

18. Should we have Query_cache enabled?

Answer : in many caches yes.
19. what are the disadvantages of having a big Query cache size?

Answer : query cache puts an additional overhead on the database. it required the db to work on invalidating the queries from the query cache.

20. what should be the optimum size of InnoDB buffer cache?

Answer : it should be the 70-80% of the memory available.

21. How do you backup InnoDB tables?

Answer : there are two ways of taking backup

1. mysqldump with --single-transaction
2. xtrabackup (part of percona)

22. How to take incremental backup in MySQL?

Answer : Using percona xtrabackup

23. Can the database be renamed in MySQL?

Answer : No.

24. How to check the table fragmentation and resolve if the fragmentation is found?

Answer : following query will list all the fragmented tabes

SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA, CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB')FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free < 0;

then run the following command on tables given by the query

alte table < table returned from pervious query > engine=innodb;

25. How to change the root password if the root password is lost?

Answer : start the Db with --skip-grants-table. change the password and restart the db in normal mode.

26. What do you do if the data disk is full?

Answer : if the data disk is full then create a soft link and move the .frm and .idb files to the linked location.

27. How to take consistent backup in mysql?

Answer : take the backup at slave after stopping the slave.

28. How do you restrict the users in mysql?

Answer : by grant and revoke commands.

29. what is advantage of having file per table parameter enabled?

Answer : point # 26 can only be done in file per table is enabled at innodb level.

30. How do you setup replication?

Answer : I will post a separate BLOG for this.. Keep reading

MySQL DBA Interview Questions - Series 1

2012-08-20T05:49:00.000-07:00

1. Describe MySQL architecture.
2. What are the major differences between MySQL 5.1 and 5.5?
3. What are the different database engines available in MySQL? (explain)
4. What are the major differences between MyISAM and InnoDB?
5. Which engine would you prefer for production OLTP environment and why?
6. What are the best installation practices for MySQL?
7. Which RAID level is best suited for MySQL?
8. How do you upgrade from one mysql version to another mysql version?
9. How many types of logs are there in mysql?
10. How do you find out slow queries in mysql?
11. How do you go through the MySQL slow query log?
12. How do you check the uptime of a mysql server?
13. If the mysql server is performing slow than how to find out the process which is causing problem.
14. What do you do if the end user complains about the performance of the DB?
15. What do you do about the slow queries?
16. Where do you change the performance parameters of mysql and by default where is the file located on a unix system?
17. Which are the important performance parameters for MyISAM and InnoDB?
18. Should we have Query_cache enabled?
19. what are the disadvantages of having a big Query cache size?
20. what should be the optimum size of InnoDB buffer cache?
21. How do you backup InnoDB tables?
22. How to take incremental backup in MySQL?
23. Can the database be renamed in MySQL?
24. How to check the table fragmentation and resolve if the fragmentation is found?
25. How to change the root password if the root password is lost?
26. What do you do if the data disk is full?
27. How to take consistent backup in mysql?
28. How do you restrict the users in mysql?
29. what is advantage of having file per table parameter enabled?
30. How do you setup replication?

MySQL Binary Installation

2012-03-24T03:22:00.000-07:00

About MySQL Binary Installation

MySQL binary installation is the easiest way of installing MySQL. If you have very little knowledge of linux you will be able to do this installation comfortably.

Steps For Binary Installation

Download the suitable binary package from mysql.com (as per the OS requirement)
create mysql user and mysql group

groupadd mysql
useradd -r -g mysql mysql

untar the binary package which you have downloaded from internet.

tar zxvf /path-to-tar-file/mysql-version-package.tar.gz

Put the untarred Directory in any directory other than / or /root/
Create symbolic link

ln -s path-to-untarred-mysql/mysql-version mysql (this will let you communicate with original untarred mysql directory without specifying long complete directory name i.e. mysql-5.5.9-linux...... )

cd mysql
Change to ownership of mysql directory

chown -R mysql .
chgrp -R mysql .

Run the mysql_install_db script to create necessary file and system files which are required to run mysql

path-to-mysql/scripts/mysql_install_db --user=mysql
or
go to scripts directory in the mysql directory
./mysql_install_db --user=mysql

chown -R root .
chown -R mysql data

With this mysql installation is complete. Above mentioned steps are mandatory for mysql installation and following steps are optional.

1. cp mysql/support-files/my.cnf /etc/my.cnf

(my.cnf is the configuration file through with you can customize the mysql environment)

2. bin/mysqld_safe --user=mysql &

(this will start mysql server)

3. cp support-files/mysql.server /etc/init.d/mysql.server

I will keep posting as and when I will get time. Cheers.........