Set root password for MariaDB

Set root password for MariaDB on windows

Problem Definition

Have you just downloaded zip archive of MariaDB, unizipped it and don’t know how to start with MariaDB? Wait, this article will walk you through few steps, so that you will know how to Set root password for MariaDB on windows and start enjoying the features of MariaDB.

Installation


Open zip installer downloaded from MariaDB download for your windows machine. After download, extract it to local folder and go to bin folder. Enter following command on command prompt. (type help on console to get help. Start MariadDB in console)

…\bin\mysqld –console

This will show the startup log for MariaDB

Startup log when MariaDB starts without any error.

Startup log when MariaDB starts without any error.

Once log says that it is ready for connection, open another window, go to bin directory and type following command

mysql

Set root password for MariaDB on windows - MariaDB Shell Prompt

MariaDB Shell Prompt

Bingoo, now you got the command prompt… Now you can even log on to MariaDB with your mysql query browser without providing any credentials. You are ready to do whatever you want in MariaDB.

 

Set Password for Root


Now, Following are steps to set root password and grant all the privileges.

mysql -u root

Above command would again bring back MariaDB Shell prompt for you

MariaDB [(none)]> use mysql;

Replace ‘NEWPASSWORD’ with your actual password in below statement.

MariaDB [(none)]> update user set password=PASSWORD(“NEWPASSWORD”) where User=’root’;

Now, flush the privileges and exit from the MariaDB server:

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit;
Bye

Now you can login to MariaDB with your new password. Open command window, go to bin folder and type following command.

mysql -u root -p

Enter the password and you are again on the shell prompt for MariaDB. Or open MySQL query browser and enter username and password to get into the MariaDB.

As MariaDB is an extension to MySQL, it inherits all the functionality and also has various enhancements and optimizations. Visit MariaDB website to know more about it.

MariaDB to MariaDB Data Replication Strategies

Data Replication Strategies

 

This blog will try to explain about the different MariaDB to MariaDB replication strategies which can be followed.

Replication Benefits

Replication is having a number of benefits, some of them includes:

  • Scalability: By having one or more slave servers, reads can be spread over multiple servers, reducing the load on the master. The most common scenario for a high-read, low-write environment is to have one master, where all the writes occur, replicating to multiple slaves, which handle most of the reads.
  • Low hardware : Having a slave system db can provide the freedom to play with that data without affecting master database. We can have all the reporting etc done on this particular database. Hence, having a very high hardware requirement on the master database can be avoided.
  • Data analysis: Analyzing data may have too much of an impact on a master server, and this can similarly be handled on a slave server, while the master continues unaffected by the extra load.
  • Backup assistance:  Backups can more easily be run if a server is not actively changing the data. A common scenario is to replicate the data to slave, which is then disconnected from the master with the data in a stable state. Backup is then performed from this server.
  • Distribution of data: Instead of being connected to a remote master, it’s possible to replicate the data locally and work from this data instead.

 

 

Approach 1 : Maria DB to Maria DB replication using Master slave configuration.

 

Data Replication

Replication is a feature allowing the contents of one or more master servers to be mirrored on one or more slave servers.

We can exert control over which data to replicate. All databases, one or more databases, or tables within a database can all be selectively replicated as well.

The main mechanism used in replication is the binary log. If binary logging is enabled, all updates to the database (data manipulation and data definition) are written into the binary log as binlog events. Slaves read the binary log from each master in order to access the data to replicate.

In this particular approach, masters and slaves do not need to be in constant communication with each other. We can take the servers offline or disconnect from the network, and when they come back, replication will continue where it left off. The slave in this case will be in access read only mode, the data copying from Master to slave will be on real time without any lag. Hence, on the BI and reporting front, we can have real time BI capabilities.

 

 

Approach 2 : Usage of Third party tools

The second approach which can be used for data replication from Master DB to Slave DB is by the usage of DB replication third party tools. One of the example which could be cited here is a tool like Tungsten. Tungsten is an open source third party data replicator tool. It not only works on MySQL but also on Maria DB. Tungsten can help solve problems like promoting masters easily from pools of slaves, replicating data between different database versions, replicating efficiently across sites, building complex topologies, and parallelizing data flow between servers. Tungsten Replicator runs equally well in cloud as well as locally hosted environments. Tungsten Replicator users range from tiny start-ups to the largest web properties on the planet.

 

 

Approach 3: using ETL. CDC implement, free version

The third approach which could be followed is to use ETL. We are having open source ETL options which includes Talend Open Studio or Kettle – Pentaho Data Integrator.

For having the master slave databases configuration implemented, we would be implementing CDC (change data capture). By CDC we can identify, capture and deliver only the changes which are made to the master database.  Frequency of the same can be set. Change Data Capture (CDC) is based on Publisher/Subscriber principle. The publisher captures the change data and makes it available to the subscribers. Implementing CDC will also help in reducing the workload on ETL since we would be sharing the information of only the changed data.

The advantages of implementing via ETL is security can be implemented like data encryption, while copying data we can have options like we can select which all tables data we want to replicate, how much amount of data we can to replicate, rules can be implemented while replicating etc.