Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

Installing and configuring MySQL



The latest version can be downloaded from http://www.mysql.com/downloads/mysql#downloads

 

Enter the server's hostname and IP address in /etc/hosts.

 

If there are going to be structure such as active and passive databases in the clusters, each servers hostname and IP address need to entered to prevent issues.

 

127.0.0.1 localhost.localdomain localhost

192.168.56.101 db01.local.domain db01
Installing MySQL from RPM packages

mount -t iso9660 /dev/scd0 /media/cd

To view the rpm packages' yum repository on the CD

vim /etc/yum.repos.d/rhel.repo[rhel]
name=RHEL
baseurl=file:///media/cd/Server>
enabled=1
gpgcheck=0yum install mysql-server mysql

Installing from tar.gz packages

mkdir -p /mysqlgroupadd mysql
useradd -d /mysql -s /bin/bash -g mysql mysql
passwd mysql -- password should be unique to this MySQL installation
-- a different password to the production environmentcd /mysql
tar -xzvf mysql-5.5.28-linux2.6-x86_64.tar.gz
ln -s mysql-5.5.28-linux2.6-x86_64 mysql

cd mysql
chown -R root .
chown -R mysql data
chgrp -R mysql .

Arrange the my.cnf file as required and copy to the /etc/ folder.
If there is an existing file from a prior version, copy it: cp /etc/my.cnf /etc/my.cnf_prior

If there is a previous MySQL setup:

mv /usr/bin/mysql /usr/bin/mysql.old -- if there is a previous version
ln -s /mysql/mysql/bin/mysql /usr/bin/mysql

 

cp support-files/mysql.server /etc/init.d/mysql
chmod 755 /etc/init.d/mysql
chkconfig --add mysql
chkconfig --level 345 mysql on

To commence:

/mysql/scripts/mysql_install_db --user=mysql
/mysql/bin/mysqld_safe --user=mysql &

View the log dumps

tail -f /mysql/mysql/data/db01.local.domain.err

 

 

/mysql/mysql/bin/mysqladmin -u root password '**PASSWORD**'
/mysql/mysql/bin/mysqladmin -u root -h host_name password '**PASSWORD**' -- IMPORTANT: Don't forget to change the host name

To check

mysql -uroot -p**PASSWORD**


View the existing databases

show databases ;

Create a database

CREATE DATABASE exampledb DEFAULT CHARACTER SET utf8;

Create a user

CREATE USER 'exampledb_user'@'localhost' IDENTIFIED BY 'password';

Granting privileges

GRANT ALL PRIVILEGES ON * . * TO 'exampledb_user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Granting privileges only on the created database

GRANT ALL PRIVILEGES ON `exampledb` . * TO 'exampledb_user'@'localhost';

 

 

show databases ;
use exampledb;

To allow connection from anywhere

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.%' IDENTIFIED BY '**PASSWORD**';

Allowing connections from specific hosts, identified by their IP address or hostname

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.7.93' IDENTIFIED BY '**PASSWORD**';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'host_name' IDENTIFIED BY '**PASSWORD**';

 


innodb_data_home_dir: the default location of the datafiles.

innodb_data_file_path: default datafile names and size information.

note: If no values have been entered into innodb_data_home_dir, then they should be included with with the path, e.g. innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M:autoextend:max:100M.

The previous line indicates that there are 3 datafiles that are 50MB large and that the maximum datafile size is 100MB.

Important: To add datafiles to MySQL the database should first be closed.

This is why it's important for programmers/developers to know how much space is required beforehand and to make annual plans for the database.

Getting the total size of ibdata3 prior to adding the datafile


[root@db01 ~]# ll /mysql/mysql/data/ibdata3
-rw-rw---- 1 mysql mysql 47185920 Oct 1 15:06 /mysql/mysql/data/ibdata3

The size in MB can be calculated by dividing 52428800 by 1024 twice.

To add data files while the size is 45MB, use the following line:


innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:45M;ibdata4:200M;:autoextend:max:500M

3 datafiles were written as 45mb and the 4th datafile was added as 200MB with a capacity of up to 500MB.
To place the datafiles in a different location, the path should be entered as /full/path/ibdata1:50M.


Please refer to dev.mysql.com/doc/refman/5.1/en/using-system-variables.html for detailed information.

mysql> SHOW VARIABLES;

 

mysql>SHOW VARIABLES LIKE 'max_join_size';
mysql>SHOW SESSION VARIABLES LIKE 'max_join_size';

 

mysql>SHOW VARIABLES LIKE '%size%';
mysql>SHOW GLOBAL VARIABLES LIKE '%size%';

 

mysql>SET sort_buffer_size=10000;
mysql>SET @@local.sort_buffer_size=10000;
mysql>SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
mysql>SET @@sort_buffer_size=1000000;
mysql>SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
mysql>SET max_join_size=DEFAULT;
mysql>SET @@session.max_join_size=@@global.max_join_size;

 

 

mysql>SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

 

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024


Monitoring the Processes from MySQL

#ps -ef|grep mysql

root 2471 1 0 07:24 ? 00:00:00 /bin/sh /mysql/mysql/bin/mysqld_safe --log-queries-not-using-indexes --log-slave-updates --datadir=/mysql/mysql/data --pid-file=/mysql/mysql/data/db01.local.domain.pid

mysql 3303 2471 0 07:24 ? 00:00:03 /mysql/mysql/bin/mysqld --basedir=/mysql/mysql --datadir=/mysql/mysql/data --plugin-dir=/mysql/mysql/lib/plugin --user=mysql --log-queries-not-using-indexes --log-slave-updates --log-error=/mysql/mysql/data/db01.local.domain.err --open-files-limit=8192 --pid-file=/mysql/mysql/data/db01.local.domain.pid --socket=/tmp/mysql.sock --port=3306

root 3730 3699 0 07:25 pts/1 00:00:00 mysql -u root -p

root 3813 3760 0 07:48 pts/2 00:00:00 grep mysql
Checking to see whether MySQL is working or not, if it is then which port it's listening to

#netstat -ntlp -- 3306

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:963 0.0.0.0:* LISTEN 2053/rpc.statd

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3303/mysqld

Watching the logs

tail -f /mysql/mysql/data/db01.local.domain.err

Status information

SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

 

 

SELECT table_name, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = 'PFM_METERING';

 

show databases ;
use sampledb

 

CREATE TABLE sample_table (
`name` varchar(255) default NULL,
`surname` varchar(255) default NULL,
`city` varchar(50) default NULL,
`country` varchar(50) default NULL
) ;

 

 

INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Jin','Eaton','Clarksville','Mayotte');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Coby','Long','Ames','United States');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Phoebe','Gay','Cedarburg','Sweden');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Blair','Dickson','Carson City','Oman');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Yoshio','Hogan','Sacramento','Tonga');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Brent','Zamora','Oil City','Bouvet Island');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Xavier','Cooley','Sheridan','Barbados');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Keegan','Schneider','White Plains','Algeria');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Baker','Carlson','Monterey Park','France');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Myra','Mcintyre','Duarte','Japan');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Vaughan','Harding','Fitchburg','Antigua and Barbuda');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Maggie','Holloway','Plano','Namibia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Solomon','Johns','Lock Haven','Western Sahara');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Jeremy','Castro','Brea','Japan');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Gloria','Walls','Madison','Togo');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Price','Keller','Trenton','Swaziland');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Mara','Johnston','Cedar Falls','Georgia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Aurelia','Hardin','Rome','Albania');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Quon','Bender','Augusta','Hungary');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Jorden','Rasmussen','Aguadilla','Guam');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Dacey','Knox','Grand Island','Pitcairn');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Merrill','Hudson','Champaign','Anguilla');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Hayfa','Noble','Utica','Cambodia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Carlos','Sanford','Aberdeen','Singapore');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Elton','Carlson','Burbank','Guinea-bissau');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Keely','Summers','Helena','French Guiana');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Wallace','Hodge','Pittsburgh','Jamaica');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Chaim','Patel','Sandy','Iceland');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Colt','Carney','Brockton','Togo');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Lila','Durham','Uniontown','Yemen');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Velma','Scott','Casper','Benin');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Maris','Cantu','Thousand Oaks','United Arab Emirates');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Edward','Alvarez','Fort Worth','Somalia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Tatum','Oneil','Buena Park','Svalbard and Jan Mayen');

 

insert into tablo_deneme select * from sample_table ;


On Microsoft operating systems

mysqldump.exe -u user_name -p database_Name > dump_file_NAME.sql

Making a compressed backup

mysqldump -u user_name -p database_Name gzip -9 > dump_file_NAME.sql.gz

Making a backup of more than one database

mysqldump -u root -p --databases database_Name01 database_Name02 database_Name03 > MY_DB_Backup.sql

Backup of all the databases

mysqldump -u root -p --all-databases > MY_DB_Backup.sql

A backup of the tables

mysqldump -u user_name -p database_Name table_name01 table_name01

Setting to remove password prompt

mysqldump.exe --user=user_name --password=password database_Name > DumpFile_NAME.sql

Restore

mysql.exe -u user_name -p name_of_database_to_be_restored < dump_file_name.sql

Copyright © 2015 Sysdba All Rights Reserved Web Designer