Setting Master Slave Mysql Replication

Configuration and Setup

On Master and Slave

edit /etc/mysql/my.cnf
# http://stackoverflow.com/questions/8605318/mysql-replication-slave-server-on-one-database#answer-8606998
# http://forums.mysql.com/read.php?35,369191,370003
# if you only want to replicate a specified database you need to add line to my.cnf
# !!! IMPORTANT in selecting the correct database name or replication will run without errors but nothing will be updated

replicate-do-db=dbname

# name reported to the master during slave registration

report_host=hostname

# give a server id

server-id=1323

# give bin log file location

log_bin=/var/log/mysql/mysql-bin.log

master server configuration file changes

# restart database

$ sudo /etc/init.d/mysql restart

# add the user the slave will use

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepassword'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

On master

Dump mysql databases WITH compression

# all databases

$ mysqldump -uUSERNAME -pPASSWORD --all-databases --master-data | gzip > master_dbname_dump.sql.gz

# individual database

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data | gzip > master_dbname_dump.sql.gz
Dump mysql databases WITHOUT compression

# individual database

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data > master_dbname_dump.sql

# View Master information in the file dumped that will set the slave.

$ grep CHANGE *sql | head -1

# show slaves that are connected

mysql> SHOW SLAVE HOSTS;

# send file to slave server

$ scp master_dbname_dump.sql username@address.com:

On Slave

edit /etc/mysql/my.cnf
the slave server has a ‘read-only’ variable because that is its purpose. (Do not use this in master-master replication)
http://dba.stackexchange.com/questions/30128/should-a-mysql-replication-slave-be-set-to-read-only#answer-30129

read_only=ON

# The purpose of the import is because both the master and slave database must be the equal at point where the binlog started recording the replication.
# import the dump.

$ mysql -uroot < master_dbname_dump.sql

# Set the slave user

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';

# Set a user that will only have an read ability.
# This is a safeguard and repetitive because setting the 'read-only' variable in my.cnf would prevent slave writing. Slave should not have writing permissions.

mysql> CREATE USER 'read_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT SELECT ON *.* TO 'read_user'@'%';

# start slave

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G 

Notes

The location of either the master or slave logs are located in /var/log/mysql.
Its a binary(.bin) file contain the queries ran to sync the slave.

# view what inside the bin, mysql view queries logs

$ mysqlbinlog /var/log/mysql/mysql-bin.000001

# show the bin name

mysql> SHOW MASTER STATUS;
mysql> SHOW BINARY LOGS;

# dba.stackexchange.com/questions/47046/why-do-the-mysql-bin-log-files-still-exist-after-a-purge-or-flush#answer-47048
# to reset all the queries and purge all binary logs. This will also do the same in the slave.

mysql> RESET MASTER;

# Sometimes there's errors in the 'slave status' you can skip queries but more error may lie ahead. This is not a permanent fix.
# https://www.howtoforge.com/how-to-repair-mysql-replication

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

# the slave is working when you see this in the status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# To detect a Master and Slave being out of sync
# Pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.
# If the values do not come back the same, then something is out-of-sync.

mysql> CHECKSUM TABLE dbname.table;

Reset everything

On master when changing the replicate user password there was an error on the slave. Then the slave needed to be reset.

On Master

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data | gzip > master_dbname_dump.sql.gz

# will clear/delete/purge all bin log. Be careful this may cause issues if there's mulitple slave to this master.

mysql> RESET MASTER;

On Slave

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;  # will clear/delete/purge all bin log.

if this error 'ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER' is because 'log_bin' was not specified in mysql configuration file my.cnf

# import the dump

$ mysql -uroot < master_dbname_dump.sql
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

the slave is working when you see in the status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master to master replication

# simply make master a slave as well.
# https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

mysql> CHANGE MASTER TO MASTER_HOST='hostname', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';

# values need to be equal where the colors match
master-master relationship

Miscellaneous

# http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o#answer-3229580
# an option to reset the slave position

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Search in all fields from every table in a database

Sometimes we need to find out a small piece of string in big Database. Like where is the configuration saved, or where is Jon’s birth date saved. This PHP code can search all the tables, all the rows and columns in a MYSQL Database. To download

OR

If you have phpMyAdmin installed use its ‘Search’ feature.

  • Select your DB
  • Be sure you do have a DB selected (i.e. not a table, otherwise you’ll get a completely different search dialog)
  • Click ‘Search’ tab
  • Choose the search term you want
  • Choose the tables to search

tags:
MySQL – Search in all fields from every table from a database
search all records mysql
anywhereindb.php

Python and MySQL

I needed a better way to prevent spammers from coming to my site.  I was manually retrieving their IP address and blocking on my .htaccess file.  However, I decided to automate it by using a python script, crontab, wordpress and MySQL.  My biggest issue was to get python to connect to mysql.

I build a script that fetches every IP address marked as spam in wordpress. Crontab runs this script every week which creates and replaces the .htaccess file with updated data.

stop_spam.py

Faster Database

You should use indexes where its required to speed up your queries. Keep them simple and don’t create indexes that are not being used by your queries. Indexes slows down INSERT and UPDATE. Indexies are beneficial on columns that are used for WHERE, ORDER BY or a SQL function.

Example in the MySQL console or terminal

select a database

mysql> USE pua;

run a query

mysql> SELECT count(date) FROM stats;

mark the time it took to process = 0.01 sec
and then make the “date” column an index

run the query again

mysql> SELECT count(date) FROM stats;

mark the time it took to process = 0.00 sec
as you can see the time went down

You can check which indexes are being used by running EXPLAIN

mysql> EXPLAIN stats;

tags:
faster, query

Access MySQL database Remotely

After a long research and unnecessary changes in order to access mysql database over a PHP script or a command line/terminal.  What is needed is to add privilege to you MySQL database and edit the my.cnf file, eg:

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;
and comment out the line in /etc/mysql/my.cnf

# bind-address = 127.0.0.1

Restart server

I’m using Ubuntu 11.10 and it didn’t need to install telnetd, or mess with the iptables. Again all is needed is to give or change mysql privileges and edit my.cnf.

Now an issue you may come across is having a firewall on your system like shorewall. Either stop shorewall (/etc/init.d/shorewall stop) or give privileges to allow access by editing the /etc/shorewall/rules and type mysql as an additional ACCEPT permission.

Now to access it outside your home network and anywhere in the world you’ll need to access your router port forward. Insert mysql port 3306 like you would do for you http 80 and ssh 22 request.

Login example

$ mysql -h192.168.1.1 -uUSERNAME -pPASSWORD

if you telnet you should see this

user@ubuntu:~ $ telnet youdomain.com 3306
Trying 13.1.24.131...
Connected to yourdomain.com.
Escape character is '^]'.
b
5.5.5-10.0.27-MariaDB-0+deb8u1 "UdxPf2?▒xU}:F2e*=66Lmysql_native_password

tags:
host

Backup MySQL

$ mysqldump -u username -p password --all-databases | gzip > /home/bigbang/Desktop/`date +%Y-%m-%d`_all_database_backup.sql.gz

crontab:

* * * * * /opt/lampp/bin/mysqldump -u root --all-databases | gzip > /opt/lampp/database_backup/`date +\%Y-\%m-\%d`_all_database_backup.sql.gz

for the date input to work in the crontab you must escape each param with a ‘\’ ==>`date +\%Y-\%m-\%d`

More on backing up MySQL and an explanation, however the latter link is not 100% correct information on the date outputs mentioned above.

To import backup:

$mysql -pPASSWORD -uUSERNAME DATABASENAME < /file/location/file.sql

tag:
backing up mysql, export, import

Create saved query in MySQL, nesting

Microsoft Access has a nice feature which lets you create queries and also link them together. This can also be done with other platforms like MySQL however the difference is individual queries cannot be saved and later linked, it must be all done in one script. Its basically multiple “SELECT * FROM” in one query.

Examples
Microsoft Access:
SELECT *
FROM table
INNER JOIN saved_query
ON table.field = saved_query.field;

MySQL:
SELECT *
FROM table as T
INNER JOIN
(SELECT * FROM another_table) as P
ON T.field = P.field

The difference between the two examples is MySQL needs to nest any additional query and also name each (eg: as T).

It’s a lot easier for me to write a query in Access. What I did was take the Access SQL code and replace (copy and paste) the saved queries with the full code.  See the complex example below.

Access code:
SELECT year.Year, [how many dividends were paid a year].*, [Price average per year].ticker, [Price average per year].AvgOfhigh, [how many dividends were paid a year].[sumofdividend]/[Price average per year].[AvgOfhigh]*100 AS yield
FROM ([year]
LEFT JOIN [how many dividends were paid a year]
ON year.Year = [how many dividends were paid a year].year)
LEFT JOIN [Price average per year]
ON year.Year = [Price average per year].year
WHERE ((([Price average per year].ticker)=[how many dividends were paid a year].[ticker]))
ORDER BY [Price average per year].ticker;

MySQL code:
SELECT t.Year, p.*, k.ticker, k.AvgOfhigh, p.`sumofdividend`/k.`AvgOfhigh`*100 AS yield
FROM (year as t
LEFT JOIN (SELECT dividend.ticker, Count(dividend.ticker) AS CountOfticker, Left(dividend.Date,4) AS year, Sum(dividend.dividend) AS SumOfdividend
FROM dividend
GROUP BY dividend.ticker, Left(dividend.Date,4)
ORDER BY dividend.ticker ) as p
ON t.Year = p.year)
LEFT JOIN (SELECT price.ticker, Left(price.Date,4) AS year, Avg(price.high) AS AvgOfhigh
FROM price
GROUP BY price.ticker, Left(price.Date,4)) as K
ON t.Year = k.year
WHERE (((k.ticker)=p.ticker))
ORDER BY k.ticker

the most common error I encounter during the conversion was that semicolon(;) and MySQL doesn’t use brackets([])

Tags:
inner join another query,  create saved query in mysql, link queries, relationship multiple queries, connect query to one another, save query as table
nesting