Wednesday, April 15, 2015

Setting up mariadb with non standard directories

Today I was configuring a mariadb 10.0.16 server with non default directories. There is a lot of things to overcome in the process.

Firstly make sure your directories are owned by mysql where you want data logs and other elements created.

The following had to be set in server.cnf and set in separate sections in some cases

socket in client section
[client]
socket=/database/mysql/mysql.sock

The rest could be safely accommodated in the mysqld section due to running only a single flavour of mysql/mariadb on the server using this cnf file.

[mysqld]



#PID file

pid-file=/var/run/mysqld/mysqld.pid



datadir=/database/mysql/dbfile

socket=/database/mysql/mysql.sock   
 
log_error=/database/mysql/serverlog/mysql_error.log

slow_query_log_file=/database/mysql/serverlog/mysql_slow.log

general_log_file=/database/mysql/serverlog/mysql_general.log

general_log_file=/database/mysql/serverlog/mysql_general.log



#INNODB logs on separate location

innodb_log_group_home_dir=/database/mysql/innodb_logs 

We are running multiple log files to allow better log handling.
Hopefully these examples will be of some use to someone contemplating the change to not using the default of /var/lib/mysql and not using /var/log for the logging and not using /var/lib/mysql as a place of other logs such as slow query log

We hit another random error related to MySQL bug #45379. We had added user= and group= to the [mysqld] section and had the server randomly setting the group_concat_max_len to 4 breaking the use of group_concat. Moving those settings to mysqld_safe resolved that. Thanks Pythian for writing about this.

[mysqld]
[mysqld_safe]

user=mysql

group=mysql


Now looking forward to getting the new servers commissioned and into production.

If all the above is not done then the database will not create files in all the expected locations but use defaults and try using /var/lib/mysql, one day this will hopefully gone, instead replaced by an intelligent installer. which will ask questions and build a config file and create directories.

One final note make sure you run mysql_install_db as the mysql user or you will have issues with starting your database once again due to permissions of files created when running the install to create the database. If you have all your config correct with directories in teh config matching actual directories then when you run mysql_install_db you wont require any parameters with it. Simple.

See ya Round

Peter









Sunday, April 12, 2015

Learning about Cassandra and Coursera

Watched the presentation produced recently by Datastax and Coursera on Cassandra and how and some of why they chose Cassandra over Mongo and Hbase to replace some of their MySQL for the website.

Some key takeaways I saw was that Cassandra better fitted their data model over HBase or MongoDB. That Mysql Sharding was difficult for some of their needs.

Coursera has a very distributed customer base and MySQL wouldn't handle multi-site information capture and distribution.

Cassandra could do rolling upgrades without downtime,this is important for Coursera, if you have ever filled out a set of responses to an assignment and then not be able to save then because the application was offline due to a database maintenance window. This was a key tenet for what replaced MySQL.
Now if your geographic requirements, ie your customer base live local to Australia or Europe, then a single site of MySQL with Galera, or using AWS RDS, may well produce suitable results. You can also use a message bus within your application to provide some resilience to the database being offline.
Rolling updates are difficult for most RDBMS and Cassandra is strong in this area.

Like so much, it's not about being cool, it's about knowing the problem you are solving to choose the right product. There is no bad database only bad implementations using the wrong product in a given situation. MongoDB seems to cop a little flak, but this is probably more about a headlong rush to get away from a perceived problem with MySQL. Often early-stage web applications ar  built with some part as Key pair store or as document store mentality. This is often simple to handle from the web development position, however, it ultimately won't scale in MySQL, or other RDBMS for that matter. So then people start looking for alternate storage ie other databases. This often creates a solution to that part of the database which is a document but causes problems with relational parts of the application. Ultimately this ends up with MongoDB being a bad choice for solving the problem.

Cassandra will be a fantastic solution as will MongoDB to the right problems. It won't solve poor design or a bad understanding of application requirements or business needs to access the data for reporting purposes. For this reason think carefully about any decision to jump off an original choice of an RDBMS such as MySQL and into the arms of a NoSQL database. It might leave a very sour taste in your mouth in the NoSQL database you choose.

Disclaimer: I have taken courses from Coursera


See ya round

Peter



Tuesday, November 11, 2014

Mariadb 10 Slave wont set up after upgrading from mariadb 5.5

We are in teh process of upgrading mariadb 5.5 databases to maridb 10. The actual upgrade process of the database has been flawless for the stand alone databases. I have started on our first replication set a pre-production system and after upgrading a slave to 10.1.14  have hit a wall

The maria10 database slave not starting up as a slave after upgrading from 5.5 The master is still running as 5.5

This is the error log

141111 10:57:10 [Note] Event Scheduler: Purging the queue. 0 events
141111 10:57:10 [Note] InnoDB: FTS optimize thread exiting.
141111 10:57:10 [Note] InnoDB: Starting shutdown...
141111 10:57:12 [Note] InnoDB: Shutdown completed; log sequence number 36086013855
141111 10:57:12 [Note] /usr/sbin/mysqld: Shutdown complete

141111 10:57:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld-nxq.pid ended
141111 10:57:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/nxq
141111 10:57:13 [Note] InnoDB: Using mutexes to ref count buffer pool pages
141111 10:57:13 [Note] InnoDB: The InnoDB memory heap is disabled
141111 10:57:13 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
141111 10:57:13 [Note] InnoDB: Memory barrier is not used
141111 10:57:13 [Note] InnoDB: Compressed tables use zlib 1.2.3
141111 10:57:13 [Note] InnoDB: Using Linux native AIO
141111 10:57:13 [Note] InnoDB: Using CPU crc32 instructions
141111 10:57:13 [Note] InnoDB: Initializing buffer pool, size = 128.0M
141111 10:57:13 [Note] InnoDB: Completed initialization of buffer pool
141111 10:57:14 [Note] InnoDB: Highest supported file format is Barracuda.
141111 10:57:14 [Note] InnoDB: 128 rollback segment(s) are active.
141111 10:57:14 [Note] InnoDB: Waiting for purge to start
141111 10:57:14 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.20-68.0 started; log sequence number 36086013855
141111 10:57:14 [Note] Plugin 'FEEDBACK' is disabled.
141111 10:57:14 [ERROR] Failed to create a socket for IPv6 '::': errno: 97.
141111 10:57:14 [Note] Server socket created on IP: '0.0.0.0'.
141111 10:57:14 [Note] Event Scheduler: Loaded 0 events
141111 10:57:14 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-log=mysqld-nxq-relay-bin' to avoid this problem.
141111 10:57:14 [ERROR] Server id not set, will not start slave
141111 10:57:14 [ERROR] Failed to create slave threads
141111 10:57:14 [ERROR] Failed to allocate memory for the Master Info structure
141111 10:57:14 [Note] /usr/sbin/mysqld: ready for connections.




This is the server.cnf it isn't as clean and tidy as it should be but it is what it is



[server]

# this is only for the mysqld standalone daemon
[mysqld]
datadir=/var/lib/mysql/nxq
log_error=/var/log/mysqld-nxq.log
socket=/var/lib/mysql/nxq/mysql.sock
pid_file=/var/run/mysqld/mysqld-nxq.pid

user=mysql
group=mysql
port=3306
binlog_format = mixed


# MASTER config
# log-bin=mysql-bin.log

expire_logs_days=7
# Autostart the event scheduler
event_scheduler=on

# SLAVE config
relay_log=mysqld-nxq-relay-bin
log_bin=mysql-bin.log
#expire_logs_days=7
log_slave_updates=1
read_only=1
#skip_slave_start=1

# Performance Tuning

innodb_buffer_pool_size=8G
innodb_file_per_table = 1
innodb_log_file_size=256M
innodb_flush_method = O_DSYNC

max_allowed_packet=700M
#table_cache=2M
#table_definition_cache=2M
#read_buffer_size=1M
#myisam_sort_buffer_size=64M
#thread_cache_size=8
query_cache_size=64M
key_buffer_size=42M
#connect_timeout=2
max_connections=300
#open_files_limit=16384
performance_schema=1

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

server_id=201

I moved the server_ID value to the [mariadb-10.0] section from the [mysqld] section to try and find a solution


Update: Turns out the reason for this behaviour was due to a typo in the init.d startup script in this line defaultfile="/etc/my.cnf" It had to be changed from another location and it had an error. Funnily enough though the strace output was not helpful as it showed what appeared to be it reading the files in the /etc/my.cnf.d directory.

How did I find it? By using the startup process of executing mysqld --help. It displays the default location parameters and i could see the server-id set correctly

See ya round

Peter

Wednesday, November 05, 2014

OSDC 2014 Day One

OSDC 2014 kicked off today and I was attending, my OSDC virginity has gone. Opens Source Developers Conference has convened on the Gold Coast

It was very interesting, I have seen some great talks getting an insight to a number of technologies
There are some people there who are madly hacking all sorts of things, Graphing technology, Arduino devices and the sustainable home, Gov hack and databases.
Its both old and young along side with only a passion about open source software
Talks about protecting yourself regarding the footprint you leave on the web from browsing and how that leaves you vulnerable to snooping about your surfung habits
Using encrypted.google.com to remove some of the inevitable jet trail you leave in metadata around the web
What about Postgres and JSON and the ability of Postgres to provide more ways to handle JSON objects? Including indexing elements of the JSON. More toi come in Postgres 9.4

Have you heard about Bluehackers, perhaps you should hopefully you never need it but one day you might just save a fellow worker with knowing about it.

Mari10DB is a leap forward, multi threaded Replication, Galera for clustering, new performance improvements with exists and in queries. GTID for replication consistency a long standing problem for MySQL and its derivatives such as mariadb. Improved performance on inserts.

Also a talk on stress and stress management with a tilt to Bluehackers

Some others are there for their first and some have been around since their inception

Tomorrow more stuff and conference dinner

It has been an interesting day with some people doing some interesting things

See ya round

Peter

Sunday, March 02, 2008

Security Skills Lacking

Apparently Security skills of the IT work force lacks generally. This is not surprising as for the most part many organisations where I work there is a lack of understanding of IT security. The government departments are very aware, generally have good policy and practise in place but I have seen many stupid breaches of good practise due to poor policy and procedures. This is where the problems often lie and its not the practitioners, who are struggling to do there job but the management who fails to understand many of the ramifications as they do not well understand the problems they are being faced with at the coal face.

This is a common problem that many parts of IT face. Management ever come up to you and say "can you sort this out for me it shouldn't take more than a few hours." Well this lack of understanding of IT from our managers is what is leaving the organisations exposed to breaches.

Clearly many managers do not understand IT law, I find this a terrible oversight that leaves many companies exposed to poor outcomes when there is a failing of there IT policy and procedures.
Recently I was doing some work on a clients site and they had me sign a piece of paper for internet access about acceptable use. It would now be considered that for that organisations they will be covered against a misdemeanour. My legal studies would lead me to the conclusion that they are likely on shaky ground, for a few reasons they have not clearly identified what are a couple of items open for interpretation. what is offensive and what is acceptable use. Now maybe another time they might just put me on a induction course that will clearly outline these, however until that is clearly stated then what may be considered offensive by the organisation may not be yours or mine interpretation.

It is these problems that management have about IT and security that are leaving the IT practitioners hanging out as without this being understood how do they understand the firefights the practitioners are wrangling inside and outside the organisations. If they can't understand this how do they get to a place where they have sufficient skills. Offering training will be good, but how does it benefit people. Does a new Checkpoint course really help solve the problem or should we look deeper at other types of training to take people out side the box to solve the problems. I certainly think a lot of management needs to go there.

See ya round

Peter

Security skills of IT workforce lacking, survey finds - Network World

Friday, December 21, 2007

Simpleology Blog Course

I'm evaluating a multi-media course on blogging from the folks at Simpleology. For a while, they're letting you snag it for free if you post about it on your blog.

It covers:

  • The best blogging techniques.
  • How to get traffic to your blog.
  • How to turn your blog into money.

I'll let you know what I think once I've had a chance to check it out. Meanwhile, go grab yours while it's still free.

Thursday, October 18, 2007

Top Ten Issues September


I have just read an article listing the top ten issues for executives in Australia at CEO Online.
Wow did I have to laugh at some of what is there. Whilst it is a serious matter for many companies, the interest of executives in the short term bottom line has meant that they have many problems. A number of these which are the doing of past hasty managements decisions.
One I had a seen at first hand is the lack of employee loyalty especially of new recruits. Now let men see, there was the J Curve and the recession we had to have(an Australian phenomenon) which gave a lot of current employees a taste of employer loyalty when things go bad. Now I fully understand that it was a difficult time financially and a number of good people, were hurt badly in all of that.

The problem is that those people and those that lost out in the dot-com crash and no they were not all IT junkies, what about the HR and other admin staff of those businesses, are all a little gun shy of employers good deeds.

Many see that employers are having a good year or two or three and some a lot more, yet they have had little financial gain in the big picture, and remember some CEO's of businesses that bit hard into there employees in the 90's are now enjoying a largesse of being CEO's in good times.

What we have is a distortion, a CEO who might have had a $1M package in 1990 has a $7M to $10M package, hmmm 700% to 1000% pay rise whilst an employee has had a modest gain of 25% or even a really good fortune of 50% so for doing more or less the same job as then his $45,000 is now $65-70,000. This employee is saying well it's only now you want to adjust my salary to a better package after I am leaving even though I asked for a better package over the last 4 pay reviews. Now how is he actually supposed to be loyal. Oh and because he applied a bit more pressure in the last pay round he knows if you have an opportunity you might want to just take him off the payroll, I mean lets face it some one younger, who probably is cheaper can do the same thing after all. Employers are having trouble of loyalty, well have a look around and make sure your people are having some good reasons to be loyal.

Do you train your managers, do you train your staff? The in principal idea of the training guarantee was good, the implementation was crap, but do you realistically spend anything like that amount on training?

Do you try and make your workplace family friendly?

Do you know your employees or if you are so big is it important for your managers to know for you about their staff and making sure that home and work are in balance. I know for many its a case of I did 80 hours starting up, and now I hope you are getting your rewards. Your employees are not as risk driven, that is why they are employees, not the competition. Make sure they are fairly rewarded and make sure you and you managers are looking after them.

I could say "Don't worry those pernickety employees they will have their day as soon as this current boom is over". That is one way to do it

What will your choice be?

See ya round

Peter