MYSQL-REPLICATION
Binary login must be enabled on master server prior to replication.
2 process will execute on the each slave server to handle replication.
1 process execute on master server per-slave server
Replication is Asynchronous which means that changes are committed to one node and then it is
propagated to N number of slaves.
Ideal for non-updating application.
REPLICATION CONFIGURATION
Master Server: 192.168.1.100
Slave Server: 192.168.1.31
Slave username: replica
Slave Password: redhat
Put the following in your master my.cnf file under [mysqld] section:
# changes made to do master
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
datadir = /var/lib/mysql/
log-bin = /var/lib/mysql/mysql-bin
# end master
Copy the following to slave’s my.cnf under [mysqld] section:
# changes made to do slave
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
datadir = /var/lib/mysql/
# end slave setup
Create user on master:
mysql > grant replication slave on *.* to replica@'192.168.1.100'
identified by 'redhat';
Do a dump of data to move to slave:
mysqldump -u root --all-databases --single-transaction --master-
data=1 > masterdump.sql
import dump on slave:
mysql < masterdump.sql
After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to
connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.100',
MASTER_USER='replica', MASTER_PASSWORD='redhat';
Let us start the slave:
mysql> start slave;
You can check the status of the slave by typing:
mysql> show slave status;
Showing posts with label MYSQL. Show all posts
Showing posts with label MYSQL. Show all posts
Wednesday, June 22, 2011
Tuesday, June 21, 2011
MYSQL COMMANDS
1. Port Used by mysql is - 3306
2. To check Mysql whether Mysql is start or stop - netstat -ntlp | grep 3306
3. To List the Database - > show databases;
4. To reveal currently logen users
> select users();
5. To list the command history
> select now ();
6. Terminal monitor mode of mysql
When we type mysql in the shell it enters in to the terminal monitor mode which means that we logged in to the database as user who is logged in to the shell.
7. To login to the databases as a user
> mysql -u root -ppassworsd
8. Connecting from remote host.
> mysql -u user -ppassword -h remotehost
By default Mysql blocks the connection from remote host. We will get an error like given below.
ERROR 1130 (00000): Host 'virt1.example.internal' is not allowed to connect to this MySQL server
9. Tighten Privilages.
Default login credential table in mysql database permit root and anonymous login from the remote host. There are three way to secure user account
a. use 'mysqladmin' program
b. use mysql terminal monitor and set the privs.
# mysqladmin -u root -p password redhat
10. Securing boath root anonymous accounts.
Disabling anonymous access to the Database
There are two type users in mysql database
a. root
b. anonymous users
Any other user is anonymous user in the mysql database concept. In some Linux distributions anonymous users can also access the MYSQL database
To test this login to mysql database from unprivileged users shell and run the following command.
> select user();
the out shows
testuser@localhost
11 .Securing DB from anonymous access
# mysql -u root -p
> show databases;
>use mysql;
> show tables;
> select * from user;
or
> select user,host from user;
+-------+-------------------+
| user | host |
+-------+-------------------+
| root | 127.0.0.1 |
| cacti | localhost |
| root | localhost |
| root | test1.example.com |
+-------+-------------------+
4 rows in set (0.05 sec)
If you can see blank lines in the above table those accounts are anonymous accounts this where non-privileged Linux/Unix/Windows mysql substitution occurs.
12. To view all the users with the corresponding password
> select user,host,password from user;
13. To Restrict all the anonymous access to the local host.
> set password for '@' localhost=password('abc123');
14. DELETING ANONYMOUS ACCOUNTS
> DELETE from user WHERE user = '.';
> FLUSH PRIVILEGES;
NOTE: this command will reread the current table in mysql to determine who's is permitted to access the DBMS.
15 . Deleting the test DB from themysql
It is also suggested that you drop test database also because databases such as test act as connecting vector for malicious users.
16. USER CREATION
Senario:-
We want to create a user that user is permitted to login from any host.
> selcect user();
> show grants;
The output of the command is as follows.
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
*.* - This means that all databases and all tables
a. Creating Another Super user in mysql
> GRANT ALL PRIVILEGES ON *.* TO 'vasanth'@'%' WITH GRANT OPTION
Running the above command will create a new super user called vasanth.
> select currect_user();
+----------------+
| current_user() |
+----------------+
| vasanth@% |
+----------------+
% - Means This user is allowded to connect from all hosts on the network.
> show grants;
B. To give permission to any user from any host
> GRANT ALL PRIVILEGES ON *.* TO ' '@'%' WITH GRANT OPTION;
> GRANT ALL PRIVILEGES ON *.* TO ' '@'%' WITH identified by 'password';
C. To drop a user run the below command
> use mysql;
> drop vasanth;
To check whether the user is deleted or not
> select user,host,password from mysql.user;
d. To allow a user vasanth from remote machine
> grant all privileges on *.* to 'vasanth'@'192.168.1.24' identified by 'redhat';
17. PRIVILEGES SCOPES
-------------------------------------
It allow us to grant privileges to the local and remote users in the database.
The general Hierarchy structure of DB is DB >> Tables >> Columns >> Routine levels
GLOBAL SCOPE LEVEL
----------------------------------
If you want to set privileges on the global scope level we need to interact with mysql.user which means that mysql being the database and user being the table.
To list the privilages in user table of mysql database
>use mysql;
>describe user;
DB SCOPE LEVEL ACCESS
--------------------------------------
If you want to set privileges on the DB SCOPE LEVEL we need to interact with mysql.host and mysql.db.
To list the privileges in host and db table
> use mysql;
> describe host;
>describe db;
GRANT PRVILAGES
---------------------------------
Task: Use grant command to create various users to create various privileges.
> GRANT ALL on *.* to 'hemanth'@'localhost' identified by 'redhat';
The above command create a user hemanth in local db with the password redhat and grant all the privileges on the all the databases.
To check whether the privilege is granted to use run the following.
> select user,host,password,Create_priv,Alter_priv from mysql.user;
The newly created hemanth user has all privileges like root except GRANT PRIVILEGES to other users to check this run the following.
> show grants for hemanth@localhost;
The output is
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for hemanth@localhost |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hemanth'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
+-------------------------------------------------------------------------------------------------------------------------+
There is no grant option
Again run the following
> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
See the above output root to has the privilege to grant privilege to other hosts.
--------------------------
To create user called hemanth1 who can connect from any host do the steps;
> use mysql;
> GRANT ALL ON * to hemanth1 identified by 'redhat';
Check the privileges granted to the user hemanth1
> select user,host,password,Create_priv,Alter_priv from mysql.user;
The output is shown below.
+----------+----------------+-------------------------------------------+-------------+------------+
| user | host | password | Create_priv | Alter_priv |
+----------+----------------+-------------------------------------------+-------------+------------
| hemanth1 | % | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | N | N |
If we use * wi9th grant command we didn't get the all privillages
To get all privileges to hemanth1 do the following
> use mysql;
> GRANT ALL ON *.* to hemanth1 identified by 'redhat';
> select user,host,password,Create_priv,Alter_priv from mysql.user;
Now hemanth1 will get all the privillages.
--------------------------
To create a limited privileged user
as root run this.
> GRANT USAGE ON *.* to hemanth2 identified by 'redhat' ;
This user hemanth2 has no privs.
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
To grant test db access to the hemanth2 user-
> GRANT ALL ON test.* to hemanth2 identified by 'redhat';
--------------------------------------
REVOKE PRIVILEGES
-------------------------------
2. To check Mysql whether Mysql is start or stop - netstat -ntlp | grep 3306
3. To List the Database - > show databases;
4. To reveal currently logen users
> select users();
5. To list the command history
> select now ();
6. Terminal monitor mode of mysql
When we type mysql in the shell it enters in to the terminal monitor mode which means that we logged in to the database as user who is logged in to the shell.
7. To login to the databases as a user
> mysql -u root -ppassworsd
8. Connecting from remote host.
> mysql -u user -ppassword -h remotehost
By default Mysql blocks the connection from remote host. We will get an error like given below.
ERROR 1130 (00000): Host 'virt1.example.internal' is not allowed to connect to this MySQL server
9. Tighten Privilages.
Default login credential table in mysql database permit root and anonymous login from the remote host. There are three way to secure user account
a. use 'mysqladmin' program
b. use mysql terminal monitor and set the privs.
# mysqladmin -u root -p password redhat
10. Securing boath root anonymous accounts.
Disabling anonymous access to the Database
There are two type users in mysql database
a. root
b. anonymous users
Any other user is anonymous user in the mysql database concept. In some Linux distributions anonymous users can also access the MYSQL database
To test this login to mysql database from unprivileged users shell and run the following command.
> select user();
the out shows
testuser@localhost
11 .Securing DB from anonymous access
# mysql -u root -p
> show databases;
>use mysql;
> show tables;
> select * from user;
or
> select user,host from user;
+-------+-------------------+
| user | host |
+-------+-------------------+
| root | 127.0.0.1 |
| cacti | localhost |
| root | localhost |
| root | test1.example.com |
+-------+-------------------+
4 rows in set (0.05 sec)
If you can see blank lines in the above table those accounts are anonymous accounts this where non-privileged Linux/Unix/Windows mysql substitution occurs.
12. To view all the users with the corresponding password
> select user,host,password from user;
13. To Restrict all the anonymous access to the local host.
> set password for '@' localhost=password('abc123');
14. DELETING ANONYMOUS ACCOUNTS
> DELETE from user WHERE user = '.';
> FLUSH PRIVILEGES;
NOTE: this command will reread the current table in mysql to determine who's is permitted to access the DBMS.
15 . Deleting the test DB from themysql
It is also suggested that you drop test database also because databases such as test act as connecting vector for malicious users.
16. USER CREATION
Senario:-
We want to create a user that user is permitted to login from any host.
> selcect user();
> show grants;
The output of the command is as follows.
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
*.* - This means that all databases and all tables
a. Creating Another Super user in mysql
> GRANT ALL PRIVILEGES ON *.* TO 'vasanth'@'%' WITH GRANT OPTION
Running the above command will create a new super user called vasanth.
> select currect_user();
+----------------+
| current_user() |
+----------------+
| vasanth@% |
+----------------+
% - Means This user is allowded to connect from all hosts on the network.
> show grants;
B. To give permission to any user from any host
> GRANT ALL PRIVILEGES ON *.* TO ' '@'%' WITH GRANT OPTION;
> GRANT ALL PRIVILEGES ON *.* TO ' '@'%' WITH identified by 'password';
C. To drop a user run the below command
> use mysql;
> drop vasanth;
To check whether the user is deleted or not
> select user,host,password from mysql.user;
d. To allow a user vasanth from remote machine
> grant all privileges on *.* to 'vasanth'@'192.168.1.24' identified by 'redhat';
17. PRIVILEGES SCOPES
-------------------------------------
It allow us to grant privileges to the local and remote users in the database.
The general Hierarchy structure of DB is DB >> Tables >> Columns >> Routine levels
GLOBAL SCOPE LEVEL
----------------------------------
If you want to set privileges on the global scope level we need to interact with mysql.user which means that mysql being the database and user being the table.
To list the privilages in user table of mysql database
>use mysql;
>describe user;
DB SCOPE LEVEL ACCESS
--------------------------------------
If you want to set privileges on the DB SCOPE LEVEL we need to interact with mysql.host and mysql.db.
To list the privileges in host and db table
> use mysql;
> describe host;
>describe db;
GRANT PRVILAGES
---------------------------------
Task: Use grant command to create various users to create various privileges.
> GRANT ALL on *.* to 'hemanth'@'localhost' identified by 'redhat';
The above command create a user hemanth in local db with the password redhat and grant all the privileges on the all the databases.
To check whether the privilege is granted to use run the following.
> select user,host,password,Create_priv,Alter_priv from mysql.user;
The newly created hemanth user has all privileges like root except GRANT PRIVILEGES to other users to check this run the following.
> show grants for hemanth@localhost;
The output is
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for hemanth@localhost |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hemanth'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
+-------------------------------------------------------------------------------------------------------------------------+
There is no grant option
Again run the following
> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
See the above output root to has the privilege to grant privilege to other hosts.
--------------------------
To create user called hemanth1 who can connect from any host do the steps;
> use mysql;
> GRANT ALL ON * to hemanth1 identified by 'redhat';
Check the privileges granted to the user hemanth1
> select user,host,password,Create_priv,Alter_priv from mysql.user;
The output is shown below.
+----------+----------------+-------------------------------------------+-------------+------------+
| user | host | password | Create_priv | Alter_priv |
+----------+----------------+-------------------------------------------+-------------+------------
| hemanth1 | % | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | N | N |
If we use * wi9th grant command we didn't get the all privillages
To get all privileges to hemanth1 do the following
> use mysql;
> GRANT ALL ON *.* to hemanth1 identified by 'redhat';
> select user,host,password,Create_priv,Alter_priv from mysql.user;
Now hemanth1 will get all the privillages.
--------------------------
To create a limited privileged user
as root run this.
> GRANT USAGE ON *.* to hemanth2 identified by 'redhat' ;
This user hemanth2 has no privs.
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
To grant test db access to the hemanth2 user-
> GRANT ALL ON test.* to hemanth2 identified by 'redhat';
--------------------------------------
REVOKE PRIVILEGES
-------------------------------
Monday, June 13, 2011
Install Mysql from source with partition enabled
Install Mysql from source with partition enabled
------------------------------
mysql-5.5.12.tar.gz
1) cmake . -LH
2) cmake .
3) make && make install
4) cd /usr/local/mysql/
5) chown -R mysql .
6) chgrp -R mysql .
7) scripts/mysql_install_db --user=mysql
8) ./bin/mysqld_safe &
rename /etc/my.cnf file
disable default mysql daemon
------------------------------
mysql-5.5.12.tar.gz
1) cmake . -LH
2) cmake .
3) make && make install
4) cd /usr/local/mysql/
5) chown -R mysql .
6) chgrp -R mysql .
7) scripts/mysql_install_db --user=mysql
8) ./bin/mysqld_safe &
rename /etc/my.cnf file
disable default mysql daemon
Wednesday, May 25, 2011
MYSQL BUILDING FROM THE SCRATCH
MYSQL INSTALLATION
# Create mysql group and user with a particular gid and uid.
/usr/sbin/groupadd -g 525 mysql
/usr/sbin/useradd -u 525 -g 525 -s /bin/bash -d /opt/mysql mysql
# Download mysql source.
cd /opt/src
wget http://www.percona.com/mysql/community/mysql-5.1.42.tar.gz
# Copy source file to mysql home directory.
cp /opt/src/mysql-5.1.42.tar.gz /opt/mysql
/bin/chown -R mysql.mysql /opt/mysql
/bin/chmod 755 /opt/mysql
# Switch to mysql user.
su - mysql
cd /opt/mysql
# Extract the source file.
tar -zxvf /opt/mysql/mysql-5.1.42.tar.gz
cd mysql-5.1.42
# Configure mysql.
./configure --prefix=/usr/local/ --enable-thread-safe-client --with-
unix-socket-path=/var/tmp/unix.sock --with-tcp-port=3306 --with-
mysqld-user=mysql --with-openssl --with-innodb --with-docs --enable-
static --localstatedir=/var/mysql/data
# Make
/usr/bin/make
# Now as root user.
cd /opt/mysql/mysql-5.1.42
/usr/bin/make install
# Copy mysql configuration file to '/etc/my.cnf'.
cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
# Uncomment innodb lines in the conf file.
/bin/sed -ie 's/#innodb/innodb/g' /etc/my.cnf
/bin/chown mysql.mysql /etc/my.cnf
/bin/chmod 600 /etc/my.cnf
# Copy the startup script to /etc/init.d/mysqld.
cp /usr/local/share/mysql/mysql.server /etc/init.d/mysqld
/bin/chmod 744 /etc/init.d/mysqld
# Add the lib files path to /etc/ld.so.conf.
/bin/echo /usr/local/lib/mysql/ >> /etc/ld.so.conf
/sbin/ldconfig
/sbin/chkconfig --add mysqld
/sbin/chkconfig mysqld on
# Create the mysql data directory.
/bin/mkdir /var/mysql
/bin/chown -R mysql.mysql /var/mysql
# As mysql user create initial databases.
su - mysql
/usr/local/bin/mysql_install_db
# Now as root start mysql daemon.
/sbin/service mysqld start
# Set a password for root user if required.
/usr/local/bin/mysqladmin -u root password 'passpass'
# Create mysql group and user with a particular gid and uid.
/usr/sbin/groupadd -g 525 mysql
/usr/sbin/useradd -u 525 -g 525 -s /bin/bash -d /opt/mysql mysql
# Download mysql source.
cd /opt/src
wget http://www.percona.com/mysql/community/mysql-5.1.42.tar.gz
# Copy source file to mysql home directory.
cp /opt/src/mysql-5.1.42.tar.gz /opt/mysql
/bin/chown -R mysql.mysql /opt/mysql
/bin/chmod 755 /opt/mysql
# Switch to mysql user.
su - mysql
cd /opt/mysql
# Extract the source file.
tar -zxvf /opt/mysql/mysql-5.1.42.tar.gz
cd mysql-5.1.42
# Configure mysql.
./configure --prefix=/usr/local/ --enable-thread-safe-client --with-
unix-socket-path=/var/tmp/unix.sock --with-tcp-port=3306 --with-
mysqld-user=mysql --with-openssl --with-innodb --with-docs --enable-
static --localstatedir=/var/mysql/data
# Make
/usr/bin/make
# Now as root user.
cd /opt/mysql/mysql-5.1.42
/usr/bin/make install
# Copy mysql configuration file to '/etc/my.cnf'.
cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
# Uncomment innodb lines in the conf file.
/bin/sed -ie 's/#innodb/innodb/g' /etc/my.cnf
/bin/chown mysql.mysql /etc/my.cnf
/bin/chmod 600 /etc/my.cnf
# Copy the startup script to /etc/init.d/mysqld.
cp /usr/local/share/mysql/mysql.server /etc/init.d/mysqld
/bin/chmod 744 /etc/init.d/mysqld
# Add the lib files path to /etc/ld.so.conf.
/bin/echo /usr/local/lib/mysql/ >> /etc/ld.so.conf
/sbin/ldconfig
/sbin/chkconfig --add mysqld
/sbin/chkconfig mysqld on
# Create the mysql data directory.
/bin/mkdir /var/mysql
/bin/chown -R mysql.mysql /var/mysql
# As mysql user create initial databases.
su - mysql
/usr/local/bin/mysql_install_db
# Now as root start mysql daemon.
/sbin/service mysqld start
# Set a password for root user if required.
/usr/local/bin/mysqladmin -u root password 'passpass'
Saturday, May 21, 2011
REMOVING ROOT PASSWORD OF MYSQL DB
Go to mysql prompt with
# mysql -u root
> use mysql;
>Select Host, User, Password from User;
> update user set password = '' where user = 'root' and host = 'localhost';
# mysql -u root
> use mysql;
>Select Host, User, Password from User;
> update user set password = '' where user = 'root' and host = 'localhost';
Subscribe to:
Comments (Atom)