MySQL

How to Allow MySQL Client to Connect to Remote MySQL server

by

By default, MySQL does not allow remote clients to connect to the MySQL database.

If you try to connect to a remote MySQL database from your client system, you will get “ERROR 1130: Host is not allowed to connect to this MySQL server” message as shown below.

$ mysql -h 192.168.1.8 -u root -p
Enter password:
ERROR 1130: Host '192.168.1.4' is not allowed to connect to this MySQL server

You can also validate this by doing telnet to 3306 mysql port as shown below, which will also give the same “host is not allowed to connect to this mysql server” error message as shown below.

Read more

Share

Recover MySQL root Password

by

 

Step # 1 : Stop mysql service
# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:[1] 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[6186]: ended [1]+ Done mysqld_safe –skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

Src: http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Share

MySQL Tidbits

by

Allow Slave to connect master:

C:\mysql>create user 'replica'@'192.168.0.110' identified by '<YOUR PASSWORD>';

NOTE: 192.168.0.110 which is the SLAVE machine‟s address.

i. mysql>grant create,insert,select,update,delete on ejbca.* to 'replica'@'192.168.0.110';

Checking if Slave can connect to Master:

C:\>mysql –u replica –h 192.168.0.100 –p

C:\>Enter password: <YOUR VALUES>

NOTE: If the mysql prompt opens then the Master machine can be accessed from the Slave machine.

 

 

Share

MySQL Replication by MySQL

by

The easiest and most straightforward method for setting up replication is to use new master and slave servers.

You can also use this method if you are setting up new servers but have an existing dump of the databases from a different server that you want to load into your replication configuration. By loading the data into a new master, the data will be automatically replicated to the slaves.

To set up replication between a new master and slave:

  1. Configure the MySQL master with the necessary configuration properties. See Section 15.1.1.1, “Setting the Replication Master Configuration”.

  2. Start up the MySQL master.

  3. Set up a user. See Section 15.1.1.3, “Creating a User for Replication”.

  4. Obtain the master status information. See Section 15.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.

    Read more

Share

MySQL Replication

by

MySQL is the relational database system of choice for open sourcers. Replication is the process of replicating data from one MySQL database server (the master) into another (the slave). We’ll go into why you would want to replicate a MySQL database in another article.

MySQL Replication
Using the master-slave configuration mentioned above, only the changes made to the master are replicated in the slave. Changes made to the slave do not affect the master.

If you follow the steps below, you can set up MySQL replication in a matter of minutes.

Read more

Share