I'm posting this more as a way of self-documenting, rather than a tried and tested method. So please do be aware of what you're doing, and that steps are from my memory, with an aim to save you time in not making the same mistakes I did ! Basically, no warranty on this, do your own due diligence before following anything here.
Intro - Why ?
I have a database locally running on Win7, MySQL v5.6.12. I can do local backups, which are great, but wanted to setup a replication system, for a hot/live-backup. MySQL has replication built in.
First problem - MySQL replication needs the master on one version and the slave on the same version or above. I'm running MySQL 5.6.12, debian will only go as high as 5.5 via apt-get for squeeze/wheezy. I tried the route of installing the testing/experimental versions via dpkg and really ended up in mess that took a long time to fix.
So, other options ... well, compile my own MySQL server from source code.
The more experienced might be able to cross compile on a more powerful machine, I opted to compile on my GoFlex Net, which is a little slow, but works in the end.
Roughly the procedure
In my /home folder :
# MySQL downloads - Generic Linux (Architecture Independent), Compressed TAR Archive v5.6.12
wget http://downloads.mysql.com/archives/get/file/mysql-5.6.12.tar.gz
tar -xvf mysql-5.6.12.tar.gz
So there's the source should be in a folder called /home/mysql-5.6.12.
In order to compile it, we'll need some developer's tools, so :
apt-get build-essential libaio1
apt-get build-dep mysql-server cmake
You now should have all the tools to compile and install MySQL from source code.
I found two "bugs" in v.5.6.12 MySQL on arm5. First was a piece of code that was expecting to be compiled on Intel platform. Second was that the MySQL embedded server doesn't appear to be compatible with arm5. The reason, I don't care really as I don't need embedded server !
So, we'll exclude the embedded server, unless you need it and want to figure out what the problem is ... don't ask me I don't know.
The bug, we'll need to patch for this version. What I'd suggest for any version not 5.6.12 is just exclude the embedded server, hopefully you won't need the patch. In any case you can apply the patch, mid-way-through, and just restart the "make" command.
Bug is described here -
http://bugs.mysql.com/bug.php?id=62769 and here -
https://bugs.launchpad.net/maria/+bug/700982
Now we could patch easily with the correct patch file and the "patch" command - info here -
http://jungels.net/articles/diff-patch-ten-minutes.html
However the patch file is for a later version and some of the other code has changed. So make life easy, I've included my already patched code. He lives in ./mysql-5.6.12/sql-common
If you're compiling any other version, well I probably won't know how to fix bugs. My solution was to keep Google'ling the errors until I found a solution, patch or enough info to be able to solve the issue.
The compilation process is - 1. cmake, 2. make then 3. make install ... easy as that 1,2,3
If you find a bug during the "make", you can fix the source code and rerun it, it won't do any uneccessary recompilation ... useful to know could save a lot of time.
If you've really balls'ed it up and need to start again from the beginning, rm the 'CMakeCache.txt' file. I also managed to remove the EMBEDDED server option by editing this file.
So, it's pretty easy really :
# probably best to do as root
cd mysql-5.6.12
# Ref : https://mariadb.com/kb/en/mariadb/documentation/getting-started/compiling-mariadb-from-source/generic-build-instructions/
# and http://www.percona.com/forums/questions-discussions/mysql-and-percona-server/12164-cmake-percona-server-5-5-34-rel32-0-error
cmake . -DBUILD_CONFIG=mysql_release -DWITH_EMBEDDED_SERVER=OFF
make
make install
Please note MariaDB and Percona are almost identical to MySQL, so you may find solutions to issues or more information on their sites or their bug sites. The MySQL documentation is extensive, a big of patience is required to find the relevant useful info !
cmake is quick, few minutes. make took several hours, and I had to fix two issues. LASTLY "make install" actually copies the good stuff into your folders, ready to run your server.
Because we didn't install via apt-get, we still need to do some stuff to get the server up and running ... grrr ... frsutrating, but helpful to know where everything is !
MySQL is configured mostly by the my.cnf file, which should be located here -
ls /etc/mysql/my.cnf
Hopefully you should be good to go with the my.cnf file there. I'd suggest strongly updating the following for a default install :
# basedir tells MySQL where the bulk of it's files are
# datadir is the default, so not absolutely neccessary.
# Also datadir is default location for MySQL log files
# error/startup/shutdown log default location is /var/log/mysql/error.log , I moved mine to the data dir
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log_error = /usr/local/mysql/data
# Listen for connection on *ANY* TCP/IP interface on this machine including localhost
bind-address = 0.0.0.0
The rest of the my.cnf is for you to tune the server, allocate memory here and there. Data can be stored in a different location, as can logs, error logs, just configure the settings in my.cnf. Do your own research on what's best, here's a reasonable starting point for a minimal resources server -
http://lowendbox.com/blog/reducing-mysql-memory-usage-for-low-end-boxes/
MySQL is recommended to be run by the linux/unix user mysql. So you may need these in my.cnf (3306 is the default port) :
[mysqld]
user = mysql
port = 3306
I think the "make install" has already installed the linux "mysql" user and "mysql" group. If not you'll have to lookup how to do that on your system useradd, groupadd I think ... this might help -
http://www.cyberciti.biz/faq/howto-linux-add-user-to-group/
Again because we self-installed, we need to setup MySQL so that it will start automagically when the box boots. Do this like so :
# Ref : http://www.debianhelp.co.uk/mysql5.htm
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
cd /etc/init.d
update-rc.d mysql defaults
If that's done properly, then this is how we stop/start/restart the server :
service mysql start
service mysql stop
service mysql restart
Don't start the server just yet, there's another step we need to do first !!!!
/usr/local/mysql/scripts/mysql_install_db --user=mysql \
--basedir=/usr/local/mysql/mysql \
--datadir=/usr/local/mysql/mysql/data
This does the initial setting up the server, installs system dbs/tables and the test database.
Last step, hopefully, get the mysql binaries in the path, so you don't have to put long pathnames in your shell everytime you want to run something !
Edit the /etc/profile file as follows (or similar on your system) :
if [ "`id -u`" -eq 0 ]; then
PATH="/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
else
PATH="/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games"
fi
export PATH
So now it's time to start the server, yay !
service mysql start
Immediately followed by :
tail -f /var/log/mysql/error.log
Hopefully the server will have started, and tell you it's listening for connections. If there's errors, then you'll have to work through these until the server has started properly ...
crtl+C out of the tail and connect to a command line MySQL client
# linux command
mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.12-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# now we're in the server ...
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.03 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| GoFlexNet2 |
+------------+
1 row in set (0.40 sec)
mysql> show processlist;
Now you can tune, load, do whatever you want with your server ...
Setting Up Replication
Lastly replication was fairly easy to setup as described here -
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql ... with the caveats :
1. I am only doing local replication MASTER 10.10.10.210 to SLAVE 10.10.10.232
2. I wouldn't recommend the dump/load procedure as described in the link, on anything but tiny databases. Reason is that I have one massive table with many indexes and my GoFlex Net would have taken days/weeks to load it. So my solution, A. copy the database schema my_database to my_database_rep on the master. B. Copy the same schema to my_database_rep on the slave. C. Establish replication. D. insert into my_database_rep.table_name select * from my_database.table_name - one table at a time, smallest tables first. This way I was able to test the replication. Rationale, let the replication system take however long it wants, in the meantime I can continue to update the master database. GoFlex Net has limited resources, so it's a way of compensating. Basic procedure :
# on master, grant all priviledges to connections from my intranet (local network)
# WITH GRANT OPTION - its required !
mysql
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.10.10.%' WITH GRANT OPTION;
# on slave
mysql
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.10.10.%' WITH GRANT OPTION;
# master to master schema copy
mysqldbcopy --skip=data --source=root@10.10.10.210 --destination=root@10.10.10.210 my_database:my_database_rep
# master to slave schema copy
mysqldbcopy --skip=data --source=root@10.10.10.210 --destination=root@10.10.10.232 my_database:my_database_rep
Setup your replication on both servers as described in the above link.
Then on master :
mysql
-- for each table, do :
insert into my_database_rep.table_name select * from my_database.table_name
During the process you can monitor the tables contents on your slave, make sure data is flowing through. I had one issue, which was resolved by examining the error log on the slave.
3. Lastly, I recommend bind-address = 0.0.0.0 in my.cnf file, so that you can connect to localhost 127.0.0.1 and your ip address locally. It's also useful in a DHCP setup where ip addresses are allocated automatically. Works fine for replication, and was suggested in one the links I researched.
That's it ... enjoy :D
Cheers
DC
Don Charisma ... because anything is possible with Charisma
My blog -
http://DonCharisma.org
Our commercial site -
http://DonCharisma.com
Edited 1 time(s). Last edit at 10/14/2014 01:19PM by DonCharisma.