Technology , Life & Beyond
Which way to count a number of rows should be faster in MySQL?
SELECT COUNT(1) FROM TABLE_NAME
When you COUNT(1) it takes in count column indexes, so it will be the best result. Mysql engine actually stores row count, it doesn’t count all rows each time you try to count all rows.
here is the codes you can use it in a script to echo the count
$counter = mysql_query(“SELECT COUNT(*) AS id FROM table_name”);
$num = mysql_fetch_array($counter);
$count = $num[“id”];
echo(“$count”);
Install Apache, Mysql , PhP Web Server
# yum install httpd mod_ssl
#/etc/init.d/httpd start
# yum install mysql mysql-server
# chkconfig –levels 235 mysqld on
# /etc/init.d/mysqld start
configure mysql
Set the MySQL service to start on boot
chkconfig –levels 235 mysqld on
Start the MySQL service
service mysqld start
Log into MySQL
mysql -u root
Set the root user password for all local domains
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘new-password‘);
SET PASSWORD FOR ‘root’@’localhost.localdomain’ = PASSWORD(‘new-password‘);
SET PASSWORD FOR ‘root’@’127.0.0.1′ = PASSWORD(‘new-password‘);
Drop the Any user
DROP USER ”@’localhost’;
DROP USER ”@’localhost.localdomain’;
Exit MySQL
exit
This happened when my cacti crashed after a power failure.
Repair a table inside database
This will repair host table in cacti databse
# mysqlcheck -r cacti host -u root -p
The following checks, optimizes and repairs all the corrupted table in cacti database.
# mysqlcheck -u root -p --auto-repair -c -o cacti
To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.
# mysqlcheck -c -u root -p --all-databases Enter password:
The following are some of the key options that you can use along with mysqlcheck.
mysql> create database ajay;
Query OK, 1 row affected (0.00 sec)
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.
Here is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.
Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.
Install mysql on master 1 and slave 1. configure network services on both system, like
Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5
On Master 1, make changes in my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin
binlog-do-db=<database name> # input the database which should be replicated
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
On master 1, create a replication slave account in mysql.
mysql> grant replication slave on *.* to ‘replication’@192.168.16.5 \
identified by ‘slave’;
and restart the mysql master1.
Now edit my.cnf on Slave1 or Master2 :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
server-id=2
master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Restart mysql slave 1 and at
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 3630
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1519187
1 row in set (0.00 sec)
Above highlighted rows must be indicate related log files and Slave_IO_Running and Slave_SQL_Running: must be to YES.
On master 1:
mysql> show master status;
+————————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————————+———-+————–+——————+
|MysqlMYSQL01-bin.000008 | 410 | adam | |
+————————+———-+————–+——————+
1 row in set (0.00 sec)
The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.
On Master2/Slave 1, edit my.cnf and master entries into it:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2
master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306
log-bin #information for becoming master added
binlog-do-db=adam
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Create a replication slave account on master2 for master1:
mysql> grant replication slave on *.* to ‘replication’@192.168.16.4 identified by ‘slave2’;
Edit my.cnf on master1 for information of its master.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306
[mysql.server]user=mysqlbasedir=/var/lib
Restart both mysql master1 and master2.
On mysql master1:
mysql> start slave;
On mysql master2:
mysql > show master status;
On mysql master 1:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.5
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql1MYSQL02-bin.000008
Read_Master_Log_Pos: 410
Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
Relay_Log_Pos: 445
Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 410
Relay_Log_Space: 445
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 103799
1 row in set (0.00 sec)
ERROR:
No query specified
Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!!
courtesy: http://www.howtoforge.com/mysql_master_master_replication