Databse - Eduguru - Page 3

mysql create user and grant permission

September 12, 2014April 19, 2015 eduguru 0 Comments create mysql user, Create user CREATE USER 'super'@'%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'; Grant Permission: GRANT ALL ON *.* TO easy@'10.0.20.212';, grant permission in mysql, mysql create user and grant permission

Create user CREATE USER ‘super’@’%’ IDENTIFIED BY PASSWORD ‘*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C’; Grant Permission: GRANT ALL ON *.* TO easy@’10.0.20.212′; Flush Privileges: FLUSH PRIVILEGES

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted

September 7, 2014February 12, 2016 eduguru 0 Comments 0 rows affected (0.00 sec) mysql> change master to master_log_file='mysql-bin.000001', 0 rows affected (0.00 sec) Now you can check, 0 rows affected (0.04 sec) mysql> start slave; Query OK, check the master relay log as follow; show master status mysql> slave stop; Query OK, master_log_pos=207078754; Query OK, Relay log read failure, relay log read failure To Resolve such kind of issue, Relay log read failure: Could not parse relay log event entry., Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted, Slave should start working.

To Resolve such kind of issue, check the master relay log as follow; mysql> slave stop; Query OK, 0 rows

MySQL Replication Slave I/O Thread States

September 4, 2014 eduguru 0 Comments a timeout occurs. At that point, after the connection to the master is established. Registering slave on master A state that occurs very briefly after the connection to the master is established. Requesting binlog dump A state that o, after the connection to the master is established. The thread sends to the master a request for the contents of its binary logs, and the relay logs have grown large enough that their combined size exceeds this value. The I/O thread is waiting until the SQL thread frees enough space by processing relay log contents so that it ca, MySQL Replication Slave I/O Thread States, starting from the requested binary log file name and position. Waiting to reconnect after a failed binlog dump request If the binary log dump request failed (due to disconnection), the state becomes Waiting for master to send event. Waiting for the slave SQL thread to free enough relay log space You are using a nonzero relay_log_space_limit value, the thread considers the connection to be broken and makes an attempt to reconnect. Queueing master event to the relay log The thread has read an event and is copying it to the relay log so that the S, the thread goes into this state while it sleeps, then tries to reconnect periodically. The interval between retries can be specified using the CHANGE MASTER TO statement or the --master-connect-retry option. Reconnecting after a failed binlog dump r, Waiting for master update The initial state before Connecting to master. Connecting to master The thread is attempting to connect to the master. Checking master version A state that occurs very briefl

Waiting for master update The initial state before Connecting to master. Connecting to master The thread is attempting to connect

MySQL Cluster

Change default MySQL Data Directory in Linux

September 4, 2014February 14, 2017 eduguru 0 Comments Change default MySQL Data Directory in Linux, default data directory or MySQL configuration file path change. So use all the command as per you system settings. 1. Stop MySQL Before making any changes, first make sure to stop mysql service # service mysqld stop 2. Change Data Directory Now copy default mysql data directory (/var/lib/mysql) to other location as per your requirement. Also set the requ, Mostly MySQL uses /var/lib/mysql directory as default data directory for Linux based systems We are showing here how to change the default directory to different directory. Follow the below steps to m

Mostly MySQL uses /var/lib/mysql directory as default data directory for Linux based systems We are showing here how to change

MySQL Cluster

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

September 4, 2014May 21, 2015 eduguru 0 Comments 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', 0 rows affected (0.00 sec) mysql> reset slave; Query OK, 0 rows affected (0.06 sec), ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log, I have noticed one issue here : mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_LOG_FILE='mysql-bin.000000', MASTER_LOG_POS=12345; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log In the error log i didn't see anything, MASTER_LOG_POS=12345; Query OK, MASTER_PASSWORD = 'pass', MASTER_PASSWORD = 'password', MASTER_PORT=53306, MASTER_USER='username', so after googling some time I found the solution: mysql> flush slave; Query OK

I have noticed one issue here : mysql> CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=53306, MASTER_USER=’username’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE=’mysql-bin.000000′, MASTER_LOG_POS=12345; ERROR

Dumping MySQL Stored Procedures, Functions and Triggers

September 4, 2014 eduguru 0 Comments a trigger or even a stored procedure. What is Triggers ? Triggers are event-driven specialized procedures, by definition, DELETE or UPDATE) occurs. When we simply execute mysqldump, Dumping MySQL Stored Procedures, Functions and Triggers, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, It automatically takes backup of triggers but it will not backup stored procedures by default. mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 my, like stored procedures and triggers. I will show in this small post how we can backup and restore these components using mysqldump. mysqldump will backup by default all the triggers but NOT the stored, like stored procedures and triggers. I will show in this small post how we can backup and restore these components using mysqldump. What is Stored Procedure ? A stored procedure, MySQL has introduced some new interesting features, then we should run something like: # mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt > outputfile.sql and this will save only the procedures/functions/triggers of the . If, then we should run something like: 1 mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt > outputfile.sql and this will save only the procedures/functions/triggers of the . If, they are stored in and managed by the database. A trigger is a SQL procedure that initiates an action on an event ( Like: INSERT

MySQL has introduced some new interesting features, like stored procedures and triggers. I will show in this small post how

How can I stop a running MySQL query?

September 3, 2014September 3, 2014 eduguru 0 Comments How can I stop a running MySQL query?, How to kill a query mysql query, I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query. Hitti, kill mysql query, so I have to reconnect. Is it possible to stop a query without killing mysql itself? mysql>show processlist; kill "number from first col";

I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too

MySQL: Count occurrences of distinct values in a column

September 3, 2014 eduguru 2 Comments MySQL: Count occurrences of distinct values, MySQL: Count occurrences of distinct values in a column

What i want to do is : example db id name —– —— 1 Mark 2 Mike 3 Paul 4

Joining to different databases table

August 21, 2014 eduguru 0 Comments join, Joining to different databases table

SELECT <…> FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;

MySQL Cluster

MYSQL Partitioning : What is and HOW To ?

August 15, 2014March 29, 2016 eduguru 3 Comments fname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', How to mysql partition, job_code INT, learn mysql, lname VARCHAR(30), MySQL, mysql partition, MYSQL Partitioning : What is and HOW To ?, PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE ); This allows to speed up e.g.: Dropping old data by simple: ALTER TABLE employees DROP PARTITION p0; Database can speed up a query like this: SELECT COUNT(*) F, separated DATE NOT NULL DEFAULT '9999-12-31', store_id INT ) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), The idea behind partitioning isn't to use multiple servers but to use multiple tables instead of one table. You can divide a table into many tables so that you can have old data in one sub table and n, What is mysql partition, you define how the data is partitioned. Simple example from the MySQL Documentation: CREATE TABLE employees ( id INT NOT NULL

The idea behind partitioning isn’t to use multiple servers but to use multiple tables instead of one table. You can