Fri, 2014-11-14 08:30anatoliydimitrov

You can use the popular programming language Python to manage data stored in MariaDB. Here is everything you need to know about connecting to MariaDB from Python for retrieving, updating, and inserting information.

Preparation and installation

If you don't already have MariaDB installed on your system, check the official MariaDB installation instructions or read the blog post on How to install MariaDB on CentOS 7.

If you want to try out Python integration with MariaDB but you don't have a database to play with, you can use the popular employees example database.

MariaDB provides …

About 6 years ago (feels like an eternity in Rails world) working at Scribd I’ve started working on porting our codebase from some old version or Rails to a slightly newer one. That’s when I realized, that there wasn’t a ruby gem to help us manage MySQL connections for our vertically sharded databases (different models on different servers). I’ve started hacking on some code to replace whatever we were using back then, finished the first version of the migration branch and then decided to open the code for other people to use. That’s how the DbCharmer ruby gem was born.

For the next few years a lot of new functionality we needed has been added to the gem, making it more complex and immensely more powerful. I’ve enjoyed working on it, developing those features, contributing to the community. But then I left Scribd, stopped being a user of DbCharmer and the situation drastically changed. For quite some time (years) I would keep …

In C (and C++) you can specify that a variable should take a specific number of bits of storage by doing “uint32_t foo:4;” rather than just “uint32_t foo”. In this example, the former uses 4 bits while the latter uses 32bits. This can be useful to pack many bit fields together.

Or, that’s what they’d like you to think.

In reality, the C spec allows the compiler to do just about anything it wants with these bitfields – which usually means it’s something you didn’t expect.

For a start, in a struct -e.g. “struct foo { uint32_t foo:4; uint32_t blah; uint32_t blergh:20; }” the compiler could go and combine foo and blergh into a single uint32_t and place it somewhere… or it could not. In this case, sizeof(struct foo) isn’t defined and may vary based on compiler, platform, compiler version, phases of the moon or if you’ve washed your hands recently.

Where …

The 2014 edition of Percona Live London brought together attendees from 30 countries to hear insightful talks from leaders in the MySQL community. The conference kicked off on Monday with a full day of tutorials followed by the very popular Community Dinner featuring a double decker bus shuttle from the conference to the event.

Tuesday started with keynote talks by representatives from MySQL, VMware, HGST, Codership, and Percona. I particularly enjoyed the talks by Tomas Ulin of MySQL (which highlighted the upcoming MySQL 5.7 release) and Robert Hodges of VMware (which focused on the evolution of MySQL). The remainder of the day was filled by six time slots of breakout sessions (30 sessions in all) broken into 10 tracks. The day wrapped up with the always popular Community Networking Reception. Attesting to the …

Useful utility to check, repair, analyze and optimize mysql tables.  Just be aware that it locks the table, so don't run on busy production tables if they are clustered as probe may time out.

mysqlcheck --auto-repair --all-databases –uroot

Eg to extract the database OPALGATEWAY  from the dump file all-06-11-11-17:00-mk-staging-1.sql.gz  .

zcat all-06-11-11-17:00-mk-staging-1.sql.gz | awk '{ if ( $0 ~ /CREATE DATABASE.*OPALGATEWAY.*/ ) a=1; if ( $0 ~ /CREATE DATABASE / && $0 !~ /CREATE DATABASE.*OPALGATEWAY.*/ ) a=0;  if (a==1) print $0 }' | gzip -  | cat - >  /tmp/OPALGATEWAY.sql.gz

slow_query_log       = 1

slow_query_log_file  = /ebill-slave-1/mysql-logs/mysqld-slow.log

long_query_time      = 6000

log-queries-not-using-indexes

log-slow-admin-statements

max_connections=13000

..and

cd mk-myacct-dbslave-2/ebill-slave-2/mysql-logs >

touch mysqld-slow.log

chown mysql:mysql mysqld-slow.log

chmod 755 mysqld-slow.log

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: mk-mysqlcluster-2-miscdb-rw.uk.intranet

                  Master_User: replusr_miscdb

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000009

          …

Execute permission for a Procedure or Function can be granted to Individually as shown below:

GRANT EXECUTE ON PROCEDURE `eonline`.`sp_getmailjoblist` TO 'eonline_rw'@'%' ;                            

GRANT EXECUTE ON FUNCTION `eonline`.`fn_getmailjobsub` TO 'eonline_rw'@'%' ;     

To Grant execute permission on all Procedures and Functions of a particular database say 'eonline' to a particular user say 'eonline_rw'  in this case, use the following syntax.

GRANT EXECUTE ON `eonline`.* TO 'eonline_rw'@'%' ;

This will grant the permissions for all the procedures and  functions of this database to be executed from any host. To restrict this to a particular host , hostname …

Anonymous MySQL accounts allow clients to connect to the server without specifying a user name. To remove anonymous accounts, connect to the server as the MySQL root user to access the mysql database, then issue the following statements:

mysql> select user,host FROM user WHERE User = '';

+------+----------------------+

| user | host                 |

+------+----------------------+

|      | localhost            |

|      | mk-myacct-dbmaster-1 |

+------+----------------------+

2 rows in set (0.00 sec)

mysql> DELETE FROM user WHERE User = '';

Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges; …