Setting up MySQL (MariaDB) master/slave replication without the downtime

I clearly don’t need to expound on the benefits of master-slave replication for your MySQL database. It’s simply a good idea; one nicety I looked forward to was the ability to run backups from the slave without impacting the performance of our production database. But the benefits abound.

Most tutorials on master-slave replication use a read lock to accomplish a consistent copy during initial setup. Barbaric! With our users sending thousands of cards and gifts at all hours of the night, I wanted to find a way to accomplish the migration without any downtime.

@pQd via ServerFault suggests enabling bin-logging and taking a non-locking dump with the binlog position included. In effect, you’re creating a copy of the db marked with a timestamp, which allows the slave to catch up once you’ve migrated the data over. This seems like the best way to set up a MySQL slave with no downtime, so I figured I’d document the step-by-step here, in case it proves helpful for others.

First, you’ll need to configure the master’s /etc/mysql/my.cnf by adding these lines in the [mysqld] section:

binlog-format   = mixed

Restart the master mysql server and create a replication user that your slave server will use to connect to the master:

CREATE USER [email protected]<<slave-server-ip>>;
GRANT REPLICATION SLAVE ON *.* TO [email protected]<<slave-server-ip>> IDENTIFIED BY '<<choose-a-good-password>>';

Note: Mysql allows for passwords up to 32 characters for replication users.

Next, create the backup file with the binlog position. It will affect the performance of your database server, but won’t lock your tables:

mysqldump -u root -p --opt --comments --dump-date --no-autocommit --all-databases --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/mysql_dump.sql

Now, examine the head of the file and jot down the values for MASTER_LOG_FILE and MASTER_LOG_POS. You will need them later:

head mysql_dump.sql -n80 | grep "MASTER_LOG_POS"

Because this file for me was huge, I gzip’ed it before transferring it to the slave, but that’s optional:

gzip ~/mysql_dump.sql

To unzip

gzip -d ~/mysql_dump.sql.gz

Or you can use tar -jcf

tar -czvf ~/mysql_dump.sql.tar.gz ~/mysql_dump.sql

To unzip

tar zxf ~/mysql_dump.sql.tar.gz

Now we need to transfer the dump file to our slave server (if you didn’t gzip first, remove the .gz bit):

scp ~/mysql_dump.sql.gz [email protected]<<slave-server-ip>>:~/

While that’s running, you should log into your slave server, and edit your /etc/mysql/my.cnf file to add the following lines:

server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1

Restart the mysql slave, and then import your dump file:

gunzip ~/mysql_dump.sql.gz
mysql -u root -p < ~/mysql_dump.sql

Log into your mysql console on your slave server and run the following commands to set up and start replication:

CHANGE MASTER TO MASTER_HOST='<<master-server-ip>>',
MASTER_LOG_FILE='<<value from above>>',
MASTER_LOG_POS=<<value from above>>;

To check the progress of your slave:


If all is well, Last_Error will be blank, and Slave_IO_State will report “Waiting for master to send event”. Look for Seconds_Behind_Master which indicates how far behind it is. It took me a few hours to accomplish all of the above, but the slave caught up in a matter of minutes. YMMV.

And now you have a newly minted mysql slave server without experiencing any downtime!

A parting tip: Sometimes errors occur in replication. For example, if you accidentally change a row of data on your slave. If this happens, fix the data, then run:


Update: In following my own post when setting up another slave, I ran into an issue with authentication. The slave status showed an error of 1045 (credential error) even though I was able to directly connect using the replicant credentials. It turns out that MySQL allows passwords up to 32 characters in length for master-slave replication.

Posted on

Why do MariaDB not start

The reason for the failure will almost certainly be written in the Error Log and, if you are starting MariaDB manually, to the console. By default, the error log is named host-name.err and is written to the data directory.

Common Locations:

  • /var/log/
  • /var/log/mysql
  • On CentOS = /var/lib/mysql/server-name.err
  • C:\ProgramData\Mysql
  • C:\Program Files\MySQL\MySQL Server x.x\data (x.x refers to the version number)

It’s also possible that the error log has been explicitly written to another location, either by changing the datadir system variable, or setting with log-error=filename – see my.cnf below.

In most cases you should be able to find out the place of the error file by doing:

mysqld --help --verbose | grep 'log-error' | tail -1
mysqld --help --verbose | grep 'datadir' | tail -1


Posted on

Setting Videojs Up For Responsive Design

Place the video html markup code on your page, in this page example the code is placed in a container div div.videocontent has 2 CSS declarations for width, it has a width:80% and a max-width:640px.

It has the main width set to 80% as then it will keep it’s width in proportion to its parent container when scaling, you need to find the percentage figure that works in your design, in this example the parent container is the div.wrapper.

I also don’t want the video displayed any wider than 640px, so that’s why the max-width is set to 640px.

So the html markup is

<div class="wrapper">
 <div class="videocontent">
	<video id="myvideo" class="video-js"..........</video>

In the Video HTML MarkUp change the width and height values from their px dimensions to auto, this makes it work in IE9!

<video id="myvideo" class="video-js vjs-default-skin" controls
 preload="auto" width="auto" height="auto" poster="my_video_poster.png"
 <source src="my_video.mp4" type='video/mp4'>

The Magic Touch

From here we need to add in 2 extra CSS rules to your CSS file, wrap these in the <head><style></style></head> in the head of your document, they need to be declared as late as possible and after the main VideoJS CSS files, if you don’t care about fullscreen you can add this in your regular CSS but if you do then add the rules as late as possible

.video-js {padding-top: 56.25%}
.vjs-fullscreen {padding-top: 0px}
<div class="wrapper">
 <div class="videocontent">
  <div id="myvideo_vjs1" class="video-js vjs-default-skin vjs-paused">
	<video id="myvideo" class="vjs-tech"..........</video>
<div class="wrapper">
 <div class="videocontent">
  <div id="myvideo_videojs1" class="video-js vjs-default-skin vjs-playing vjs-fullscreen>
	<video id="myvideo" class="vjs-tech"..........</video>

This .video-js is the class that is generated by VideoJS and is a class containing the video tag.

Posted on

How to Work out the Correct Padding Top for your Video

The 56.25% is the sweet spot that creates the area for the video based on the 16*9 aspect ratio

To work out a video aspect ratio padding %, divide the height by the width, for example if we had a non standard aspect ratio for a video 512px wide by 380px high, 512/380=1.34736842105263, so 134.73% would bet the sweet spot for that size.

The 2nd CSS declaration .vjs-fullscreen removes the padding so the video is not pushed down in fullscreen mode, the .vjs-fullscreen class is added when the fullscreen button is pressed.

Thats it, the VideoJS should scale as the viewport changes. To see the set up with minimal code in the source here is a barebones example. This set up has been tested and verified in IE 9 & 10, latest Chrome, Firefox and Safari and iOS devices.

Here is an example of VideoJS in a Bootstrap framework.

Posted on

How to get size of mysql database?

Simple 🙂

SELECT table_schema                                        "DB Name", 
   ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema; 

Posted on

How to use bash to run a file listet with simple commands

you can make a shell script with those commands, and then chmod +x <>, and then just run it by


you can also just run it with a shell, for example:

bash example.txt
sh example.txt

Its very simple to write a bash script

Mockup sh file:

sudo command1
sudo command2 
sudo commandn

Posted on

Sed – Delete one or more lines from a file

sed -i “.bak” ‘text to locate’ /root/file_to_delete_lines.txt

Posted on

FFmpeg add Metadata to QuickTime/MOV/MP4/M4A

Basic Usage

FFmpeg has a free-form command line option that allows the user to specify key/value pairs for encoding metadata. The option is -metadata and is used as such:

 ffmpeg -i inputfile -metadata title="Movie Title" -metadata year="2010" outputfile

Whether the metadata key/value pairs are actually encoded into the output file is dependent upon the file format being muxed. Many formats only support a handful of metadata keys. This page documents which keys FFmpeg will encode into which formats.

QuickTime/MOV/MP4/M4A/et al.

The following table shows the metadata keys that FFmpeg honors when muxing a QuickTime file. The low-level identifier column lists the atom name that the format uses to encode the data on disc, which is not interesting to most readers. For the interested but uninitiated, the notation, e.g., ‘\251nam’ indicates a 4-byte code consisting of the byte A9 in hexadecimal (or 251 in octal) followed by the ASCII characters ‘n’, ‘a’, and ‘m’.

Key iTunes field Low-level identifier
“title” Name ‘\251nam’
“author” Artist ‘\251ART’
“album_artist” Album Artist ‘aART’
“album” Album ‘\251alb’
“grouping” Grouping ‘\251grp’
“composer” Composer ‘\251wrt’
“year” Year ‘\251day’
“track” Track Number ‘trkn’
“comment” Comments ‘\251cmt’
“genre” Genre ‘\251gen’
“copyright” ?? ‘\251cpy’
“description” Description ‘desc’
“synopsis” Information dialog when selecting “Show Description” in context menu ‘ldes’
“show” Show ‘tvsh’
“episode_id” Episode ID ‘tven’
“network” ?? ‘tvnn’
“lyrics” Lyrics ‘\251lyr’

Further, the MOV muxer encodes libavformat version string into the ‘\251too’ field. FFmpeg does not allow this key to be overridden from the command line

Posted on

FFMPEG combine or merge several video into one video (Concatenating media files)

If you have several videos that you need to combine into one video, here is a couple of tricks. tested with ffmpeg version N-71718-gfa2d3b6-syslint

Option one fx combine dvd files .vob into a mp4

This example combines two vob files because the movie was split into several vobs.

ffmpeg -i "concat:/media/dvd/VIDEO_TS/VTS_01_1.VOB|/media/dvd/VIDEO_TS/VTS_01_2.VOB" -acodec libfaac -aq 100 -ac 2 -vcodec libx264 -vpre slow -crf 24 -threads 0 output.mp4

Option two

Create a file mylist.txt with all the files you want to have concatenated in the following form (lines starting with a # are ignored):

# this is a comment
file '/path/to/file1'
file '/path/to/file2'
file '/path/to/file3'

Note that these can be either relative or absolute paths. Then you can stream copy or re-encode your files:

ffmpeg -f concat -i mylist.txt -c copy output

It is possible to generate this list file with a bash for loop, or using printf. Either of the following would generate a list file containing every *.wav in the working directory:

# with a bash for loop
for f in ./*.wav; do echo "file '$f'" >> mylist.txt; done
# or with printf
printf "file '%s'\n" ./*.wav > mylist.txt

If your shell supports process substitution (like Bash and Zsh), you can avoid explicitly creating a list file and do the whole thing in a single line. This would be impossible with the concat protocol (see below). Make sure to generate absolute paths here, since ffmpeg will resolve paths relative to the list file your shell may create in a directory such as “/proc/self/fd/”.

ffmpeg -f concat -i <(for f in ./*.wav; do echo "file '$PWD/$f'"; done) -c copy output.wav
ffmpeg -f concat -i <(printf "file '$PWD/%s'\n" ./*.wav) -c copy output.wav
ffmpeg -f concat -i <(find . -name '*.wav' -printf "file '$PWD/%p'\n") -c copy output.wav

You can also loop a video. This example will loop input.mkv 10 times:

for i in {1..10}; do printf "file '%s'\n" input.mkv >> mylist.txt; done
ffmpeg -f concat -i mylist.txt -c copy output.mkv

Concat protocol

While the demuxer works at the stream level, the concat protocol works at the file level. Certain files (mpg and mpeg transport streams, possibly others) can be concatenated. This is analogous to using cat on UNIX-like systems or copy on Windows.


ffmpeg -i "concat:input1.mpg|input2.mpg|input3.mpg" -c copy output.mpg

If you have MP4 files, these could be losslessly concatenated by first transcoding them to mpeg transport streams. With h.264 video and AAC audio, the following can be used:

ffmpeg -i input1.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts intermediate1.ts
ffmpeg -i input2.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts intermediate2.ts
ffmpeg -i "concat:intermediate1.ts|intermediate2.ts" -c copy -bsf:a aac_adtstoasc output.mp4

If you’re using a system that supports named pipes, you can use those to avoid creating intermediate files – this sends stderr (which ffmpeg sends all the written data to) to /dev/null, to avoid cluttering up the command-line:

mkfifo temp1 temp2
ffmpeg -i input1.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts temp1 2> /dev/null & \
ffmpeg -i input2.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts temp2 2> /dev/null & \
ffmpeg -f mpegts -i "concat:temp1|temp2" -c copy -bsf:a aac_adtstoasc output.mp4

All MPEG codecs (H.264, MPEG4/divx/xvid, MPEG2; MP2, MP3, AAC) are supported in the mpegts container format, though the commands above would require some alteration (the -bsf bitstream filters will have to be changed).

Sources: and

Posted on