Month: August 2023

https://i.kinja-img.com/gawker-media/image/upload/c_fill,f_auto,fl_progressive,g_center,h_675,pg_1,q_80,w_1200/b2dea49a7c3b0f4b22fdf23d46d9b7d6.jpg

Lifehacker

https://theawesomer.com/photos/2023/08/laser_record_t.jpg

Link

Compact discs use a laser to read data and convert that to music. Vinyl records, on the other hand, use a needle to pick up vibrations. Artist Tee Ken Ng used a laser pointer to do something else with a record, exposing a glow-in-the-dark record to laser light as it spun on a turntable. He made some more complicated patterns in this second video.

@teekenng Drawing with a laser pointer on a record that I covered with glow in the dark vinyl. The laser energises the phosphors in the #photoluminescent vinyl causing it to glow. As they lose their charge the older lines fade creating the trail effect. #hypnotic #vinylart #laser #spiral #liveanimation ♬ Space Walk – Lemon Jelly

The Awesomer

http://img.youtube.com/vi/ytyms4cNSbo/0.jpg

You will laugh at the lying politicians and be pissed at the same time.

Gun Free Zone

https://www.percona.com/blog/wp-content/uploads/2023/08/Use-systemd-in-Linux-to-Configure-and-Manage-Multiple-MySQL-Instances-200×115.jpeg

This blog describes how to configure systemd for multiple instances of MySQL. With package installations of MySQL using YUM or APT, it’s easy to manage MySQL with systemctl, but how will you manage it when you install from the generic binaries?

Here, we will configure multiple MySQL instances from the generic binaries and manage them using systemd.

Why do you need multiple instances on the same server?

We will do that, but why would you need multiple instances on the same host in the first place? Why not just create another database on the same instance? In some cases, you will need multiple instances on the host. 

1. You can have a host with two or three instances configured as a delayed replica of the source server with SQL Delay of, let’s say, 24hr, 12hr, and 6/3hrs.
2. Backup testing. You can run multiple instances on a server to test your backups with the correct version and configs.
3. We split databases by function/team to give each team full autonomy over their schema, And if someone screws up, it breaks their cluster, not all databases. However, larger instances are more economical as not all MySQL servers will always need maximum resources. So you put multiple MySQL servers on a single machine instead of multiple databases inside one MySQL instance. Better failure handling, similar cost. But yes, do not put all nodes of the same cluster on the same host, but you have multiple nodes on the same host of different clusters. 
4. Cases where (in very large sharded deployments) a user will install multiple mysqlds per server to reduce contention, i.e., they get more performance per 2-socket server with four or eight mysqlds than one.  AFAIK Facebook does this.

The original motivation for FB was due to different hardware generations, especially between regions/data centers. For example, an older data center may have smaller/less powerful machines, so they run fewer mysqld per host there to compensate. There were other exceptions, too, like abnormally large special-case-shard needing dedicated machines.

That said, other performance motivations mentioned above did play into it, especially before the days of multi-threaded replication. And I agree that in the modern age of cloud and huge flash storage, the vast majority of companies will never need to consider doing this in prod, but there is always a chance of its need. 

Install MySQL

To install and use a MySQL binary distribution, the command sequence looks like this:

yum install  libaio1 libaio-dev numactl
useradd -r -g mysql -s /bin/false mysql
groupadd mysql
cd /usr/local/
tar xvfz /root/Percona-Server-8.0.19-10-Linux.x86_64.ssl101.tar.gz
ln -s /usr/local/Percona-Server-8.0.19-10-Linux.x86_64.ssl101/ mysql
cd /data/
mkdir -p /data/mysql/{3306,3307}/data
chown -R mysql:mysql /data
chmod 750 -R /data/mysql/{3306,3307}/data

Create MySQL configuration for each instance

Below is an example of the first instance I placed in /etc/prod3306.cnf. My naming convention is prod3306 and prod3307. I then place that naming convention in the configuration filename  /etc/prod3306.cnf. I could have done my.cnf.instance or instance.my.cnf.

[root@ip-172-31-128-38 share]# cat  /etc/prod3306.cnf

[mysqld@prod3306]
datadir=/data/mysql/3306
socket=/data/mysql/3306/prod3306.sock
mysqlx_socket=/data/mysql/3306/prod3306x.sock
log-error=/data/mysql/prod3306.err
port=3306
mysqlx_port=33060
server-id=1336
slow_query_log_file=/data/mysql/3306/slowqueries.log
innodb_buffer_pool_size = 50G
lower_case_table_names=0
tmpdir=/data/mysql/3306/tmp/
log_bin=/data/mysql/3306/prod3306-bin
relay_log=/data/mysql/3306/prod3306-relay-bin
lc_messages_dir=/usr/local/mysql/share


[mysqld@prod3307]
datadir=/data/mysql/3307
socket=/data/mysql/3307/prod3307.sock
mysqlx_socket=/data/mysql/3307/prod3307x.sock
log-error=/data/mysql/prod3307.err
port=3307
mysqlx_port=33070
server-id=2337
slow_query_log_file=/data/mysql/3307/slowqueries.log
innodb_buffer_pool_size = 50G
lower_case_table_names=0
lc_messages_dir=/usr/local/mysql/share
tmpdir=/data/mysql/3307/tmp/
log_bin=/data/mysql/3307/prod3307-bin
relay_log=/data/mysql/3307/prod3307-relay-bin

The directory lc_messages_dir=/usr/local/mysql/share  is required when your MySQL binaries base directory is not the default one, so I had to pass the path for it — otherwise, MySQL won’t start. 

Initialize instance

Initialize your database and get the temporary password for the database from the error log file so you can log in and update the passwords after the MySQL instances are started.

ln -s /usr/local/mysql/bin/mysqld /usr/bin
mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/data/mysql/3307 --lower_case_table_names=0
mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/data/mysql/3306 --lower_case_table_names=0

Configured the systemd service

Create the SYSTEMD base configuration at /etc/systemd/system/mysql@.service and place the following contents inside. This is where the naming convention of the MySQL instances comes into effect. In the SYSTEMD configuration file, %I will be replaced with the naming convention that you use. 

[root@ip-172-31-128-38 share]# cat /usr/lib/systemd/system/mysqld@.service
# Copyright (c) 2016, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
#
# systemd service file for MySQL forking server
#

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/mysql/mysqld-%i.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd %I
# Start main service
ExecStart=/usr/bin/mysqld --defaults-file=/etc/prod3306.cnf --defaults-group-suffix=@%I --daemonize --pid-file=/data/mysql/mysqld-%i.pid $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 65536
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
[root@ip-172-31-128-38 share]#

Reload daemon

systemctl daemon-reload

Start MySQL

systemctl start mysqld@prod3307

systemctl start mysqld@prod3306

Enable MySQL service

systemctl enable mysqld@prod3307

systemctl enable mysqld@prod3306

Error log for each instance

[root@ip-172-31-128-38 3307]# tail -5 /data/mysql/prod3306.er

tail: cannot open ‘/data/mysql/prod3306.er’ for reading: No such file or directory

[root@ip-172-31-128-38 3307]# tail -5 /data/mysql/prod3306.err

2023-07-10T05:26:42.521994Z 0 [System] [MY-010910] [Server] /usr/bin/mysqld: Shutdown complete (mysqld 8.0.19-10)  Percona Server (GPL), Release 10, Revision f446c04.

2023-07-10T05:26:48.210107Z 0 [System] [MY-010116] [Server] /usr/bin/mysqld (mysqld 8.0.19-10) starting as process 20477

2023-07-10T05:26:52.094196Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2023-07-10T05:26:52.112887Z 0 [System] [MY-010931] [Server] /usr/bin/mysqld: ready for connections. Version: '8.0.19-10'  socket: '/data/mysql/3306/prod3306.sock'  port: 3306  Percona Server (GPL), Release 10, Revision f446c04.

2023-07-10T05:26:52.261062Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/data/mysql/3306/prod3306x.sock' bind-address: '::' port: 33060

root@ip-172-31-128-38 3307]# tail -5 /data/mysql/prod3307.err

2023-07-10T05:26:36.032160Z 0 [System] [MY-010910] [Server] /usr/bin/mysqld: Shutdown complete (mysqld 8.0.19-10)  Percona Server (GPL), Release 10, Revision f446c04.

2023-07-10T05:26:58.328962Z 0 [System] [MY-010116] [Server] /usr/bin/mysqld (mysqld 8.0.19-10) starting as process 20546

2023-07-10T05:27:02.179449Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2023-07-10T05:27:02.198092Z 0 [System] [MY-010931] [Server] /usr/bin/mysqld: ready for connections. Version: '8.0.19-10'  socket: '/data/mysql/3307/prod3307.sock'  port: 3307  Percona Server (GPL), Release 10, Revision f446c04.

2023-07-10T05:27:02.346514Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/data/mysql/3307/prod3307x.sock' bind-address: '::' port: 33070

[root@ip-172-31-128-38 3307]#

Conclusion

Utilizing systemctl to control MySQL significantly simplifies the management of MySQL instances. This approach facilitates the easy configuration of multiple instances, extending beyond two, and streamlines the overall administration process. However, it is essential to be mindful of memory allocation when setting up multiple MySQL instances on a single server. Allocating memory appropriately for each MySQL instance ensures sufficient overhead and optimal performance.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

Download Percona Monitoring and Management Today

Planet MySQL

https://i0.wp.com/lefred.be/wp-content/uploads/2023/08/Screenshot-from-2023-08-18-15-01-29.png?w=1688&ssl=1

There are plenty GUI and Web application used to monitor a MySQL server. But if you are long time MySQL DBA, you might have used (and abused) Innotop !

I loved it ! And I even became maintainer of it. This particular task became more and more complicated with the different forks and their differences. Also, let’s be honest, Perl saved my life so many times in the past… but this was in the past. These days, having Perl on a system is more complicated.

But Innotop is still very popular in the MySQL world and to help me maintaining it, I would like to welcome a new member in the maintainer group: yoku0825. Tsubasa Tanaka has been a long time user and contributor of Innotop and I’m sure will keep to good work.

I’ve tried to find an alternative to Innotop, and I even wrote my own clone in Go for MySQL 8.0: innotopgo. But some limitations of the framework I used affected my motivation…

But some time ago, Charles Thompson contacted me about a new tool he was writing. He was looking for feedback.

The tool was very promising and finally this week he released it !

The tool is written in Python 3 and it’s very easy to modify it to contribute code.

Dolphie, the name of the tool, is available on GitHub and can easily be installed using pip:

$ pip install dolphie

Dolphie is already very complete and supports several new features available in MySQL 8.0.

For example I do like the Transaction History, that display the statement that were done inside a running transaction:

Dolphie also integrates the error log from Performance_Schema:

And it also allows searches:

Trending

Dolphie also provides some very interesting trending graphs that can be used to look at performance issues.

This is an example:

The best way to discover all its possibilities is to install and test it.

Conclusion

Dolphie is a brand new Open Source (GPLv3) tool for MySQL DBAs, made for the Community by the Community. It’s very easy to get involved, as Dolphie is written in Python, and Charles, its author, is very responsive in implementing features and solving problems.

I really encourage you to test it, submit bugs, feature requests and, of course, contributions !

Welcome Dolphie and long life !

Planet MySQL

https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2023/08/mongodb-queries-you-must-know.jpg

 use db_name 

 use db_name
db.dropDatabase()
 

 db.createCollection("collection_name")
 

 db.collection_name.insertOne({"Name":"Idowu", "Likes":"Chess"})
 

 db.collection_name.insertOne([{"Name":"Idowu", "Likes":"Chess"}, {"Language": "Mongo", "is_admin": true}])
 

 db.collection_name.insertMany([{"Name":"Idowu", "Likes":"Chess"}, {"Name": "Paul", "Likes": "Wordle"}])
 

 db.collection_name.find()
 

 db.collection_name.find().limit(2)
 

 db.collection_name.find({"Likes":"Wordle"}, {"_id":0, "Name":1})
 

 db.collection_name.find({"Likes":"Chess", "Age":{"$gt":21}}, {"_id":0, "Name":1})
 

 db.collection_name.find({"Likes":"Chess"}).sort({"Age":1})
 

 db.collection_name.find({"Likes":"Chess"}).sort({"Age":-1})
 

 db.collection_name.updateOne({"Name":"Sandy"}, {"$set":{"Name":"James", "email":"example@gmail.com"}})
 

 db.collection_name.updateOne({"Name":"Sandy"}, {"$unset":{"email":"example@gmail.com"}})
 

 db.collection_name.updateOne({"Name":"Pete"}, {"$push":{"items":"Plantain"}})
 

 db.collection_name.updateOne({"Name":"Pete"}, {"$push":{"items": {"$each":["Almond", "Melon"]}}})
 

 db.collection_name.updateOne({"Name":"Pete"}, {"$pull":{"items":"Plantain"}})
 

 db.collection_name.updateOne({"Name":"Pete"}, {"$pull":{"items": {"$in":["Almond", "Melon"]} }}) 

 db.collection_name.deleteOne({"Name":"IDNoble"})
 

 db.collection.deleteMany({"Likes":"Chess"})
 

 db.collection.createIndex({"Name":1})
 

 db.collection.getIndexes()
 

 db.sales.aggregate([{$group:{"_id":"$Section", "totalSold":{$sum:"$Sold"}}}, {$project:{"_id":0, "totalSold":1, "Section":"$_id"}}])
 

MakeUseOf

https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2023/08/network-switch-cables.jpg

 sudo apt install nethogs 

 sudo pacman -S nethogs 

 sudo dnf install nethogs 

 sudo nethogs 

 sudo setcap "cap_net_admin,cap_net_raw+pe" /path/to/nethogs 

 which nethogs 

 lsof -i 

 sudo apt install lsof 

 sudo pacman -S lsof 

 sudo dnf install lsof 

 sudo apt install net-tools
 

 sudo pacman -S net-tools 

 sudo dnf install net-tools 

 netstat -p 

 netstat -pc 

 netstat -p | grep 'systemd' 

 sudo netstat  

MakeUseOf

https://stackabuse.com/assets/images/icon-information-circle-solid.svg

Introduction

Pandas is a powerful Python library for working with and analyzing data. One operation that you might need to perform when working with data in Pandas is selecting columns based on their string prefix. This can be useful when you have a large DataFrame and you want to focus on specific columns that share a common prefix.

In this Byte, we’ll explore a few methods to achieve this, including creating a series to select columns and using DataFrame.loc.

Select All Columns Starting with a Given String

Let’s start with a simple DataFrame:

import pandas as pd

data = {
    'item1': [1, 2, 3],
    'item2': [4, 5, 6],
    'stuff1': [7, 8, 9],
    'stuff2': [10, 11, 12]
}
df = pd.DataFrame(data)
print(df)

Output:

   item1  item2  stuff1  stuff2
0      1      4       7      10
1      2      5       8      11
2      3      6       9      12

To select columns that start with ‘item’, you can use list comprehension:

selected_columns = [column for column in df.columns if column.startswith('item')]
print(df[selected_columns])

Output:

   item1  item2
0      1      4
1      2      5
2      3      6

Creating a Series to Select Columns

Another approach to select columns based on their string prefix is to create a Series object from the DataFrame columns, and then use the str.startswith() method. This method returns a boolean Series where a True value means that the column name starts with the specified string.

selected_columns = pd.Series(df.columns).str.startswith('item')
print(df.loc[:, selected_columns])

Output:

   item1  item2
0      1      4
1      2      5
2      3      6

Using DataFrame.loc to Select Columns

The DataFrame.loc method is primarily label-based, but may also be used with a boolean array. The ix indexer for DataFrame is deprecated now, as it has a number of problems. .loc will raise a KeyError when the items are not found.

Consider the following example:

selected_columns = df.columns[df.columns.str.startswith('item')]
print(df.loc[:, selected_columns])

Output:

   item1  item2
0      1      4
1      2      5
2      3      6

Here, we first create a boolean array that is True for columns starting with ‘item’. Then, we use this array to select the corresponding columns from the DataFrame using the .loc indexer. This method is more efficient than the previous ones, especially for large DataFrames, as it avoids creating an intermediate list or Series.

Applying DataFrame.filter() for Column Selection

The filter() function in pandas DataFrame provides a flexible and efficient way to select columns based on their names. It is especially useful when dealing with large datasets with many columns.

The filter() function allows us to select columns based on their labels. We can use the like parameter to specify a string pattern that matches the column names. However, if we want to select columns based on a string prefix, we can use the regex parameter.

Here’s an example:

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({
    'product_id': [101, 102, 103, 104],
    'product_name': ['apple', 'banana', 'cherry', 'date'],
    'product_price': [1.2, 0.5, 0.75, 1.3],
    'product_weight': [150, 120, 50, 60]
})

# Select columns that start with 'product'
df_filtered = df.filter(regex='^product')

print(df_filtered)

This will output:

   product_id product_name  product_price  product_weight
0         101        apple           1.20             150
1         102       banana           0.50             120
2         103       cherry           0.75              50
3         104         date           1.30              60

In the above code, the ^ symbol is a regular expression that matches the start of a string. Therefore, '^product' will match all column names that start with ‘product’.

Next: The filter() function returns a new DataFrame that shares the data with the original DataFrame. So, any modifications to the new DataFrame will not affect the original DataFrame.

Conclusion

In this Byte, we explored different ways to select columns in a pandas DataFrame based on a string prefix. We learned how to create a Series and use it to select columns, how to use the DataFrame.loc function, and how to apply the DataFrame.filter() function. Of course, each of these methods has its own advantages and use cases. The choice of method depends on the specific requirements of your data analysis task.

Planet Python

https://i.kinja-img.com/gawker-media/image/upload/c_fill,f_auto,fl_progressive,g_center,h_675,pg_1,q_80,w_1200/d8dad39f888525d8adec05e95b0dbfda.png

Lifehacker

So, you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.

Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right.

See that subscribe button? Grab our newsletter!

Steps to MySQL Management

Here are the steps that are required for MySQL management as a DBA:

1. Installation

The “yum” tool is your friend.  If you’re using Debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL.

It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweaks and fixes. Also, if you’re not sure, go with MySQL 5.5 for new installations.

$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
$ yum install Percona-Server-client-55
$ yum install Percona-Server-shared-55
$ yum install Percona-Server-shared-compat
$ yum install Percona-Server-server-55

The last command will create a fresh database for you as well.

Already have data in an existing database? Then you can migrate between MySQL and Oracle.

2. Setup Replication

MySQL replication is a process you’ll need to setup over and over again. Its statement based in MySQL. A lot of INSERT, UPDATE, DELETE & CREATE statements are transferred to the slave database, and applied by a thread running on that box.

The steps to setup are as follows:

1. lock the primary with FLUSH TABLES WITH READ LOCK;
2. issue SHOW MASTER STATUS and note the current file & position
3. make a copy of the data. You can dump the data:

$ mysqldump -A –single-transaction > full_primary.mysql

Alternatively, you can use xtrabackup to take setup replication without locking!

4. copy the dump to the slave database (scp works, but rsync is even better as it can restart if the connection dies).
5. import the dump on the slave box (overwrites everything so make sure you got your boxes straight!)

$ mysql < full_primary.mysql

6. point to the master

mysql> change master to
> master_user=’rep’,
> master_password=’rep’,
> master_host=’10.20.30.40′,
> master_log_file=’bin-log.001122′,
> master_log_pos=11995533;

7. start replication & check

mysql> start slave;
mysql> show slave statusG;

You should see something like this:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3. Analyze Slow Query & Tune

If you’re managing an existing MySQL database and you hit a performance blip, it’s likely due to something that has changed. You may be getting a spike in user traffic, that’s new! Or you may have some new application code that has been recently deployed, that’s new SQL that’s running in your database. What to do?

If you haven’t already, enable the slow query log:

mysql> set global slow_query_log=1;
mysql> set global long_query_time=0.50;

Now wait a while. A few hours perhaps, or a few days. The file should default to

/var/lib/mysql/server-slow.log

Now analyze it. You’ll use a tool from the percona toolkit to do that. If you haven’t already done so, install the percona toolkit as well.

$ yum install percona-toolkit
$ pt-query-digest /var/lib/mysql/server-slow.log > /tmp/server-report.txt

Once you’ve done that “less” the file, and review. You’ll likely see the top five queries account for 75% of the output. That’s good news because it means less query tuning. Concentrate on those five and you’ll get the most bang for your buck.

Bounce your opinions about the queries off of the developers who build application code. Ask them where the code originates. What are those pages doing?

Check the tables, are there missing indexes? Look at the EXPLAIN output. Consider tuning the table data structures, multi-column, or covering indexes. There is typically a lot that can improve these troublesome queries.

4. Monitoring Command Line Tools

You’ll want to have a battery of day-to-day tools at your disposal for interactive monitoring of the database. Don’t go overboard. Obsessive tuning means obsessively turning knobs and dials. If there are no problems, you’re likely to create some. So, keep that in mind.

innotop is a “top” like utility for monitoring what’s happening inside your little database universe.  It’s probably already available through yum and the “epel” repository:

$ yum install innotop

First edit the .my.cnf file and add:
[client]
user=root
password=mypw

From there you should be able to just fire up innotop without problems.

mysqltuner is a catch all tool that does a once over of your server, and gives you some nice feedback.  Get a copy as follows:

$ wget mysqltuner.pl

Then run it:
$ chmod +x mysqltuner.pl
$ ./mysqltuner.pl

Here are a couple of useful mysql shell commands to get database information:

mysql> show processlist;
mysql> show innodb status;
mysql> show status;

There is also one last tool which can come in handy for reviewing a new MySQL server. Also, from percona toolkit, the summary tool. Run it as follows:

$ pt-summary

5. Backups

You absolutely need to know about backups if you want to sleep at night. Hardware and database servers fail, software has bugs that bite. And if all that doesn’t get you, people make mistakes. So-called operator error will surely get you at some point. There are three main types:

1. cold backups

With the database shutdown, make a complete copy of the /var/lib/mysql directory, along with perhaps the /etc/my.cnf file. That together amounts to a cold backup of your database.

2. hot backups

There has been an enterprise tool for MySQL that provides this for some time. But we’re all very lucky to also have the open source Percona xtrabackup at our disposal. Here’s a howto using it for replication setup.

3. logical backups

These will generate a file containing all the CREATE statements to recreate all your objects, and then INSERT statements to add data.

$ mysqldump -A > my_database_dump.mysql

6. Review existing servers

The percona toolkit summary tool is a great place to start.

$ pt-summary

Want to compare the my.cnf files of two different servers?

$ pt-config-diff h=localhost h=10.20.30.40

Of course, you’ll want to review the my.cnf file overall. Be sure you have looked at these variables:

tmp_table_size
max_head_table_size
default_storage_engine
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
log_slow_queries
log_bin
innodb_log_buffer_size
innodb_log_file_size
innodb_buffer_pool_size
key_buffer_size (for MyISAM)
query_cache_size
max_packet_size
max_connections
table_cache
thread_cache_size
thread_concurrency

7. Security essentials

The output of the pt-summary and mysqltuner.pl scripts should give you some useful information here. Be sure to have passwords set on all accounts. Use fewer privileges by default, and only add additional ones to accounts as necessary.

You can use wildcards for the IP address but try to be as specific as possible. Allow for a subnet, not the whole internet ‘10.20.30.%’ for example instead of just ‘%’.

Also keep in mind that at the operating system or command line level, anyone with root access can really mess up your database. Writing to the wrong datafile or changing permissions can hose a running database very quickly.

8. Monitoring

Use a monitoring system such as Nagios to keep an eye on things.  At minimum check for:

1. connect to db
2. server load average
3. disk partitions have free space
4. replication running – see above IO & SQL running status messages
5. no swapping – plenty of free memory

9. Ongoing Maintenance

Periodically it’s a good idea to review your systems even when they’re running smoothly. Don’t go overboard with this however. As they say if it isn’t broke, don’t fix it.

1. check for unused & duplicate indexes
2. check for table fragmentation
3. perform table checks (if using MyISAM)

10. Manage the Surprises

MySQL is full of surprises. In the Oracle world you might be surprised at how arcane some things are to setup, or how much babysitting they require. Or you might be surprised at how obscure some tuning & troubleshooting techniques are. In the MySQL world there are big surprises too. Albeit sometimes of a different sort.

1. Replication Checksums

One that continues to defy my expectations is those surrounding replication. Even if it is running without error, you still have more checking today. Unfortunately, many DBAs don’t even know this!

That’s because MySQL replication can drift out of sync without error. We go into specific details of what things can cause this, but more importantly how to check and prevent it, by bulletproofing MySQL with table checksums.

2. Test & Confirm Restores of Backups

Spinup a cloud server in Amazon EC2, and restore your logical dump or hotbackup onto that box. Point a test application at that database and verify that all is well. It may seem obvious that a backup will do all this.

But besides the trouble when a filesystem fills up, or some command had the wrong flag or option included. There can be even bigger problems if some piece or section of the database was simply overlooked.

It’s surprising how easy it is to run into this trouble. Testing also gives you a sense of what restoring time looks like in the real world. A bit of information your boss is sure to appreciate.

If you made it this far, you know you want to grab the newsletter.

Conclusion

That’s all about how to manage MySQL as a DBA. Hopefully, you have found this guide exceptional from other ordinary guides to MySQL management. For any further queries, our comment box is always open for you. Thanks for reading!

The post Accidental DBA’s Guide to MySQL Management appeared first on iheavy.

Planet MySQL