There are several types of indexes are available in MySQL: B-Tree Indexes: Normal Indexes – Normal indexes are the most basic indexes, and have no restraints such as uniqueness. It can contain duplicate value. Unique Indexes – Unique indexes are the same as “Normal” indexes with one difference: all values of the indexed column(s) must … Continue Reading
Category Archives: Mysql
How heartbeat helpful for MySQL Replication
Heartbeat is a project from Linux-HA. http://www.linux-ha.org/wiki/Heartbeat. Heartbeat will be helpful when we would have pair and/or more servers which are configured to replicate in circular fashion. I.e In MySQL Replication, if master will be fail we have to do manually failover or we have to make script which will manage this thing. But if … Continue Reading
How to install MySQL 5.1 on Ubuntu 10.10
Its easy to install MySQL on ubuntu but you must have updated your repository for apt-get utility. If you haven’t than run below command to update it. shell> apt-get update After that, to install MySQL 5.1 on ubuntu run below command. shell>sudo apt-get install mysql-server-5.1 you’ll have to give your password so it will start … Continue Reading
How to monitor performance of MySQL Server
Performance Monitoring of MySQL Server: Following are the command which we can use for session or server level performance for MySQL server. SHOW GLOBAL STATUS – shows global server status SHOW LOCAL STATUS - This is used for session level server status Have to check following values to know how server works. Aborted_clients : Usually … Continue Reading
MySQL DBA Responsibilities
Overview of DBA duties Server startup/shutdown Mastering the mysqladmin administrative client Using the mysql interactive client User account maintenance Log file maintenance Database backup/copying Hardware tuning Multiple server setups Software updates and upgrades File system security Server security Repair and maintenance Crash recovery Preventive maintenance Understanding the mysqld server daemon Performance analysis Obtaining and Installing … Continue Reading
How to reset MySQL root password
1. Stop mysql service shell> /etc/init.d/mysql stop 2. Start to MySQL server without password. shell> mysqld_safe –skip-grant-tables & 3. Connect to mysql server using mysql client. shell> mysql -u root Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 107Server version: 5.1.49-1ubuntu8.1 (Ubuntu)Type ‘help;’ or ‘h’ for help. Type … Continue Reading
How to secure MySQL server
OS level Security: Never run the MySQL server as the linux ‘root’ user. Always create the separate user for MySQL and start/stop MySQL server with it. If the data directories are located under $mysql_home/data dir then make sure that directory and all subdirectories should have only read and write privileges for the mysql user. Always … Continue Reading
How to convert MySQL database from Latin1 to Utf8
Once client decided to switch database from Latin1 to UTF8. I was working on a Linux platform at the time but you can do the same thing in Windows too. Some people have a site that is using a Mysql database which uses the Latin1 character set and they want to convert their database to … Continue Reading