Siddhant Sawant

Percona/MySQL

[siddhantsawant]

How to pause ALTER using pt-online-schema-change

Well, first things first , to use pt-online-schema-change for OSC , we need to know what it does . To know more on how the tool works you can read through a blog explaining its working and its documentation.  This blog is demonstration of pt-osc’s parameter --pause-file  to pause the already running copy of rows from the table to the new table. We have table cost_customers with  below structure.

| cost_customers | CREATE TABLE `cost_customers` (

 `time` char(13) NOT NULL,

 `customer` int(5) NOT NULL,

 `cost` decimal(10,6) NOT NULL DEFAULT '0.000000',

 `profit` decimal(10,6) NOT NULL DEFAULT '0.000000',

 PRIMARY KEY (`time`,`customer`),

 KEY `customer` (`customer`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

 And let’s say we want to modify the column profit . So , the use of --pause-file is , the parameter takes string as input and execution  of the tool will be paused while the file specified by this parameter exists.

The command used for pt-osc is

pt-online-schema-change --execute

--pause-file=/home/siddhant/pt-osc_pause_file

--no-drop-old-table 

--alter="modify column profit decimal(10,5) NOT NULL DEFAULT '0.00000'" 

h=localhost,D=sidd_test,t=cost_customers

If the tool finds the file /home/siddhant/pt-osc_pause_file at the mentioned location , the tool will pause its execution and wait for default of 60 seconds to check again.Note the user should have read access to the directory. Here is it’s execution ,

[root@siddhant_2 ~]# pt-online-schema-change  --execute --check-interval=2 --pause-file=/home/siddhant/pt-osc_pause_file  --no-drop-old-table  --alter="modify column profit decimal(10,7) NOT NULL DEFAULT '0.00000'"  h=localhost,D=sidd_test,t=cost_customers

Operation, tries, wait:

 analyze_table, 10, 1

 copy_rows, 10, 0.25

 create_triggers, 10, 1

Altering `sidd_test`.`cost_customers`...

Creating new table...

Created new table sidd_test._cost_customers_new OK.

Altering new table...

Altered `sidd_test`.`_cost_customers_new` OK.

2017-05-24T08:26:46 Creating triggers...

2017-05-24T08:26:46 Created triggers OK.

2017-05-24T08:26:46 Copying approximately 72372 rows...

Sleeping 60 seconds because /home/siddhant/pt-osc_pause_file exists

Sleeping 60 seconds because /home/siddhant/pt-osc_pause_file exists

I created /home/siddhant/pt-osc_pause_file file when pt-osc started its execution.

As soon as pause-file was deleted

[root@siddhant_2 siddhant]# rm -rfv /home/siddhant/pt-osc_pause_file

removed `/home/siddhant/pt-osc_pause_file'

The execution continued and rows were copied to the new table and alter was completed.

2017-05-24T08:51:27 Copied rows OK.

2017-05-24T08:51:27 Analyzing new table...

2017-05-24T08:51:27 Swapping tables...

2017-05-24T08:51:28 Swapped original and new tables OK.

Not dropping old table because --no-drop-old-table was specified.

2017-05-24T08:51:28 Dropping triggers...

2017-05-24T08:51:28 Dropped triggers OK.

Successfully altered `sidd_test`.`cost_customers`

[siddhantsawant]

How Elections Work in MongoDB

When a secondary server(say node2) cannot reach a primary(node1), it will contact all the other members in replica set (node 3,4...) and request that it should be elected as primary.

These other members(node 3,4,..) do several checks

Can they reach the primary server(node1) that the member(node2) which is seeking election cannot reach?

Is the member seeking election(node2) is in sync , i.e whether it up to date with replication

Is there any other server with a higher priority available who should be elected instead?

If a member seeking election (node 2) receives “yes” from a majority of the set,it becomes primary.If even one server vetoes the election, the election is cancelled.

A member vetoes an election when it knows any reason that the member seeking election (node2) shouldn't become primary. The member seeking election (node 2) must be up to date with replication, as far as the members it can reach know. All replicated operations are strictly ordered by ascending timestamp, so the candidate must have operations later than or equal to any member it can reach. A common point of confusion is that members always seek election for themselves. For simplicity’s sake, neighbors cannot “nominate” another server to be primary, they can only vote for it if it is seeking election. Why an odd number is recommended in replSet for MongoDB

 If you loose a single member in a 3 members replica set, it's the same as loosing a single member in a 4 members replica set: you still gain quorum majority and a new primary can be elected (the replset can still elect a new master by majority). On the other hand, if you loose two members in a 3 members replica set or a 4 members replica set (or n/2 members of n-members replica set) - again - the impact is the same: No new leader can be voted by election. References:  http://docs.mongodb.org/master/core/replica-set-elections/

[siddhantsawant]

Adding User in MongoDB

Adding User in MongoDB

Let’s get started with routine MongoDB administration work. One of the first priorities for Database administrators is to ensure their Database is secure. The best way to handle security with MongoDB is to run it in a trusted environment,ensuring that only trusted users are able to connect to the server.

Each database in a MongoDB instance can have any number of users.When security is enabled, only authenticated users of a database are able to perform read or write operations.There are two special databases: users in the admin and local databases can perform operations on any database.After authenticating, admin users are able to read or write from any database and are able to perform certain admin-only commands, such as listDatabases or shutdown.

Let’s add some users to “admin” and example “mymongo” database with different roles.

Note — The ‘addUser’ shell helper is DEPRECATED. Use ‘createUser’ instead

Adding a “root” user in mongodb .(admin database) >use admin switched to db admin >db.createUser( … { … user: “superuser”, … pwd: “pass”, … roles: [ “root” ] … } … ) Successfully added user: { “user” : “superuser”, “roles” : [ “root” ] }

Adding a “readOnly” user (mymongo database) >db.createUser( { user: “db_read1”, pwd: “pass”, roles: [ { role: “read”, db: “mymongo” }]})

Adding a “readWrite” user (mymongo database) db.createUser( {  user: “db_write”,  pwd: “pass”,roles:  [{ role: “readWrite”, db: “mymongo” } ] })

Now restart the server, this time adding the – -auth command-line option or in mongodb.conf as auth=true to enable security.

When we first connect, we are unable to perform any operations (read or write) on the mymongo database.

#mongo mymongo -u db_read1 -p MongoDB shell version: 2.6.4 Enter password: connecting to: mymongo

>db.collection1.findOne() { “_id” : ObjectId(“54da125004655bd6347700d3”), “x” : 1 } > db.collection1.insert({o:99}) WriteResult({ “writeError” : { “code” : 13, “errmsg” : “not authorized on mymongo to execute command { insert: \”collection1\”, documents: [ { _id: ObjectId(’54da150f7a2bdd3ffd58bb93′), o: 99.0 } ], ordered: true }” } })

After authenticating as the “db_read1” user, however, we are able to perform a simple find. When we try to insert data, we are met with a failure because of the lack of authorization.

So we try with “db_write” user.

# mongo mymongo -u db_write -p MongoDB shell version: 2.6.4 Enter password: connecting to: mymongo > > db.collection1.insert({u:23}) WriteResult({ “nInserted” : 1 })

root(superuser) user, who is able to perform operations on any database.

]# mongo admin -u superuser -p MongoDB shell version: 2.6.4 Enter password: connecting to: admin > use mymongo switched to db mymongo > db.collection1.insert({k:4444}); WriteResult({ “nInserted” : 1 })

With sharding, the admin database is kept on the config servers, so shard mongods have no idea it even exists. Therefore, as far as they know, they are running with authentication enabled but no admin user.Thus, shards will allow a local client to read and write from them without authentication. However, if you are worried about clients running locally on shards and connecting directly to them instead of going through the mongos, you may wish to add admin users to your shards.

Make sure that  replica sets on which you are creating users ,are already shards(i.e added) in the cluster. If you create an admin user and then try to add the mongods as a shard the addShard command will not work (because the cluster already contains an admin database).

FYR: http://docs.mongodb.org/manual/tutorial/add-admin-user/ http://docs.mongodb.org/manual/reference/built-in-roles/

[siddhantsawant]

RESET MYSQL ROOT PASSWORD

RESET MYSQL ROOT PASSWORD

Stop mysqld .

Locate the .pid file that contains the server’s process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system’s host name. You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:

[root@siddhant ~]# ps -ef | grep -i mysql

root 1393 1 0 07:57 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/var/lib/mysql –pid-file=/var/lib/mysql/siddhant.pid

mysql 1519 1393 0 07:57 ? 00:00:35 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib/mysql/plugin –user=mysql –log-error=/var/lib/mysql/siddhant.err –pid-file=/var/lib/mysql/siddhant.pid

[root@siddhant ~]# kill `cat /var/lib/mysql/siddhant.pid`

[root@siddhant ~]# ps -ef | grep -i mysql

root 3721 3637 3 13:03 pts/0 00:00:00 grep -i mysql

Restart it with the –skip-grant-tables option.

[root@siddhant ~]# mysqld_safe –skip-grant-tables & [root@siddhant ~]# ps -ef | grep mysql

root 5008 3637 0 14:13 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –skip-grant-tables

mysql 5087 5008 26 14:13 pts/0 00:00:09 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib/mysql/plugin –user=mysql –skip-grant-tables –log-error=/var/lib/mysql/siddhant.err –pid-file=/var/lib/mysql/siddhant.pid

This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use –skip-grant-tables in conjunction with –skip-networking to prevent remote clients from connecting.

Connect to the mysqld server with this command:

shell> mysql

Issue the following statements in the mysql client. Replace the password with the password that you want to use.

mysql> UPDATE mysql.user SET Password=PASSWORD(‘secret’) WHERE User=’root’;

mysql> FLUSH PRIVILEGES;

The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

You should now be able to connect to the MySQL server as root using the new password.

Stop the server.

Restart it normally (without the –skip-grant-tables and –skip-networking options).

[root@siddhant ~]# /etc/init.d/mysql stop

Shutting down MySQL.. SUCCESS!

[root@siddhant ~]# /etc/init.d/mysql start

Starting MySQL…………. SUCCESS!

[root@siddhant ~]# mysql -u root -p Enter password: Your MySQL connection id is 1 Server version: 5.6.22 MySQL Community Server (GPL) Copyright © 2000, 2014, 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. mysql> mysql>

There you go, you got youself a new password .