Vmwarelabs

12/03/2023

Import/Export Large MYSQL Databases From CmdLine
Jump to navigationJump to search
Copy/Export a Large Database

MYSQL has no 'Copy' function. You create a copy by dumping the database with mysqldump.

To dump the database and gzip it at the same time, use the following. This will prompt you for your password.

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz
Import a Large Database

If you want to replace the database with a fresh dump created by the above process, do the following.

First, unzip the file.

gzip -d [/path_to_file/DBNAME].sql.gz
Get to a mysql prompt (you will be asked for your password.)

[/path_to_mysql/]mysql -u [USERNAME] -p
Then do the following to wipe out the old database and replace it with the new dump:

SHOW DATABASES;
DROP DATABASE [DBNAME];
CREATE DATABASE [DBNAME];
USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

12/03/2023

Restoring a .sql dump which is in Innodb format to MyISAM format
Jump to navigationJump to search
Suppose the dump file is using Innodb as engine

Find his dump file test.sql

head -100 test.sql
You can see the engine is innodb

If the customer needs it to restore in Myisam format we need to edit theis dump file

First take a backup of dump file

cp test.sql test.sql_bkp
Then execute the following perl script to change all Innodb terms in test.sql to MyISAM

perl -p -i -e "s/Innodb/MyISAM/" test.sql
Then restore the database

In some cases, database may be created by executing the commands in the dump file (for example if .sql file contains create database xoops)

So inorder to restore the dump just execute

mysql < test.sql

12/03/2023

Replication - check slave integrity
Jump to navigationJump to search
mk-table-checksum is an excellent tool for verifying whether slave is in sync with the master. I have tested it in one of the mysql setup in MAS servers. The steps to follow are,

On the master,

Create a database. You can name anything. In this case I have named it test.

Then create a table to store the checksum values. Here I call it checksum. The syntax for creating table is

CREATE TABLE checksum (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
boundaries char(100) NOT NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL,
PRIMARY KEY (db, tbl, chunk)
);

Then create a user like

grant all on *.* to 'slvchk'@10.10.2.26 identified by 'chkSum';
where slvchk is the username and chkSum is the pasword. 10.10.2.26 is the ip of the master.

Then execute the following command...

mk-table-checksum -u slvchk -pchkSum --ignore-databases mysql,information_schema,test,mysql_monitor,remote --replicate test.checksum
--empty-replicate-table 10.10.2.26
(--ignore-databases is used to ignore the databases we dont want to check. --empty-replicate-table deletes any previous checksums existing in the checksum table.)

The above command will populate the checksum table with the checksum value. As a result of replication, the the checksum table will be populated with data in the slave also.

During it's ex*****on, keep an eye on the load, as it might increase the load on the system. So better do it in off-peak hours on production servers.

We get an output like the following..

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
thefinerthings08 acl_actions 0 10.16.55.86 InnoDB 70 43247b13 0 NULL NULL NULL
thefinerthings08 acl_permissions 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 addresses 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 amazon_unsync_ipns 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 attachments 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 auto_status_changes 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 autocomplete_searches 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 autocomplete_stopwords 0 10.16.55.86 InnoDB 233 a4dc9c1b 0 NULL NULL NULL
thefinerthings08 ban_countries 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 ban_email_domains 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 ban_ips 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 ban_referrers 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL
thefinerthings08 batch_print_files_content 0 10.16.55.86 MyISAM 0 NULL 0 NULL NULL NULL
thefinerthings08 batch_prints 0 10.16.55.86 MyISAM 0 NULL 0 NULL NULL NULL
thefinerthings08 bundles 0 10.16.55.86 InnoDB 4 414aaf5a 0 NULL NULL NULL
thefinerthings08 cache 0 10.16.55.86 InnoDB 2 6c5d45d3 0 NULL NULL NULL
thefinerthings08 cart_swap 0 10.16.55.86 InnoDB 0 NULL 0 NULL NULL NULL

Wait for the above command to finish. It will take time depending on the no and size of databases. So better do it in screen.

When this command has finished executing, check the slave lag.

On the slave,

show slave status\G
If the seconds behind master value is not zero, wait for it to become zero.

After making sure seconds behind master is zero, execute the following command on master...

On the master,

mk-table-checksum -u slvchk -pchkSum --ignore-databases mysql,information_schema,test,mysql_monitor,remote --replicate test.checksum
--replicate-check 1 10.10.2.26
(--replicate check 1 will check the masters' slaves upto one level, ie 1 slave. If 2, then upto two levels ie two slaves)

You will get an output like the following...

Differences on P=3306,h=10.16.55.75
DB TBL CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
admin_homeseason addresses 0 0 1 1=1
admin_homeseason categories 0 0 1 1=1
admin_homeseason cms_pages 0 0 1 1=1
admin_homeseason crons 0 0 1 1=1
admin_homeseason errors 0 0 1 1=1
admin_homeseason files 0 0 1 1=1
admin_homeseason ip2country 0 0 1 1=1
admin_homeseason items 0 0 1 1=1
admin_homeseason semaphores 0 0 1 1=1
admin_homeseason sites 0 0 1 1=1
admin_homeseason taxes 0 0 1 1=1
admin_homeseason users 0 0 1 1=1
admin_homeseason user_groups 0 0 1 1=1
admin_homeseason vars 0 0 1 1=1
admin_powerswabs categories 0 0 1 1=1
admin_powerswabs crons 0 0 1 1=1
admin_powerswabs errors 0 0 1 1=1
admin_powerswabs ip2country 0 0 1 1=1
In the above output 10.16.55.75 is the slave of this master.

The 4th column CNT_DIFF column is what we are interested in.

A CNT_DIFF of zero means slave is in sync with the master,

If CNT_DIFF is a positive integer value, then there is that much rows extra on slave(this should not happen in a master-slave setup).

If CNT_DIFF is a negative integer value, then there is that much rows missing on slave.

After finding the tables with differences, we can use mk-table-sync to sync the slave to master.

12/03/2023

Replication - purge master logs
Jump to navigationJump to search
Syntax

PURGE {MASTER | BINARY} LOGS TO 'log_name'

PURGE {MASTER | BINARY} LOGS BEFORE 'date'

Deletes all the binary logs listed in the log index prior to the specified log or date. The logs also are removed from the list recorded in the log index

file, so that the given log becomes the first.

Example:

PURGE MASTER LOGS TO 'mysql-bin.010';

PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

The BEFORE variant's date argument can be in 'YYYY-MM-DD hh:mm:ss' format. MASTER and BINARY are synonyms.

This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of

the logs you are trying to delete, this statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the

logs it has yet to read, the slave will be unable to replicate after it comes up.

To safely purge logs, follow this procedure:

On each slave server, use SHOW SLAVE STATUS to check which log it is reading.

Obtain a listing of the logs on the master server with SHOW MASTER LOGS.

Determine the earliest log among all the slaves. This is the target log. If all the slaves are up to date, this is the last log on the list.

Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)

Purge all logs up to but not including the target log.

12/03/2023

MySQL Replication
Jump to navigationJump to search
Install mysql in both machines.

The first machine will act as Master M1 -10.10.2.1 The Second machine will act as slave S1 -10.10.2.2

In the master M1

vi /etc/my.cnf

Add the following lines

server-id =1 value should be unique
log-bin=db-bin
relay-log=db-relay-bin

Now in M1 create a user replication such that S1 have the privilege to access all the databases of M1

mysql -p
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@'10.10.2.2' IDENTIFIED BY 'pass1';
mysql>exit
Restart the master mysql

/etc/init.d/mysqld restart
mysql -p
mysql>show master status \G;
The output will be something like below

mysql> show master status\G;

File: db-bin.000003
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB: 1 row in set (0.00 sec)

ERROR: No query specified

SLAVE S1

vi /etc/my.cnf
Add the following lines

server-id=2
master-host = 10.10.2.1
master-user = replication
master-password = pass1
master-port = 3306

Replication Master data cannot be set in slave my.cnf for MySQL 5.5 Server, though the my.cnf, Set only server-id on my.cnf.

It is also good to add the host entries from mysql command prompt and it should be like this

mysql -p
mysql> CHANGE MASTER TO
MASTER_HOST='10.10.2.1',

MASTER_USER='replication',
MASTER_PASSWORD='pass1',

MASTER_LOG_FILE='db-bin.000003' ;
Remember the master log file is the same as the output shown by show master status \G; in the master server

mysql -p
mysql> start slave ;
mysql> show slave status \G;

If there is no errors and the entries below

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Sample Output

mysql -p
mysql> show slave status\G;

Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.2.26
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000027
Relay_Log_Pos: 248
Relay_Master_Log_File: db-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 546
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>

12/03/2023

Copying MySQl Database from one server to another
Jump to navigationJump to search
MySQL Database can be copied from one server to another by performing following steps.

1.Create a mysql dump

mysqldump Database > backname.sql
2.Transfer the backup file(backname.sql) from server A to Server B

3.Enter the other server.

4.Enter mysql.

5.Create a database by that name.

6.Restore the database using:

12/03/2023

Timeout error occurred trying to start MySQL Daemon
Jump to navigationJump to search
/etc/rc.d/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.

Starting MySQL: [FAILED]

First check /var/log/mysqld.log

Goto /var/lib/mysql

cd /var/lib/mysql
look at the permissions and ownerships of mysql directory under /var/lib/mysql . If it is not mysql then change it as follows :

chown -R mysql.mysql mysql

chmod 755 mysql
Start mysql

/etc/rc.d/init.d/mysqld start

12/03/2023

MySQL Commands
Jump to navigationJump to search
Creating and Deleting MySQL Databases

Create database data:

mysql> create database data;
Query OK, 1 row affected (0.00 sec)
mysql>
Delete database data:

mysql> drop database data;
Query OK, 0 rows affected (0.00 sec)
mysql>
Creating User

We create a user using :

mysql> create user rekha@localhost identified by "rekha";
Query OK, 0 rows affected (0.00 sec)
mysql>
Granting Privileges to Users

We have to create a MySQL username and password with privileges to access the database.

sql> grant all privileges on database.* to username@"servername" identified by 'password';
The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.

sql> flush privileges;

Running MySQL Scripts To Create Data Tables

We can run the script by logging into MySQL as the MySQL root user and automatically importing all the script file's commands with a ' desc login1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | UNI | NULL | |
| age | int(11) | YES | | NULL | |
| phone | varchar(15) | YES | | NULL | |
| s*x | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
mysql>
6. Viewing The Contents Of A Table

mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | hello |
+------+-------+
1 row in set (0.07 sec)
mysql> [/code]
You can specify range using : SELECT * FROM `your_table` LIMIT 0, 10

7. Delete a table

mysql> drop table 'table_name';
Query OK, 0 rows affected (0.08 sec)

mysql>

Mysql Repair/Check

Repair

cd /var/lib/mysql
myisamchk -r phpbb_sessions.MYI
Check

cd /var/lib/mysql
myisamchk -a phpbb_sessions.MYI

12/03/2023

MYSQL

MYSQL is the world's most popular open source database. MySQL AB is the sole owner of the MySQL server source code.

Installing MySQL
We have to install the MySQL server and MySQL client RPMs on the server. The client RPM gives you the ability to communicate with the server,

There are a number of supporting RPMs that may be needed, so the yum utility may be the best RPM installation method to use.
You can download the source rpm from http://dev.mysql.com/downloads/
To configure MySQL to start at boot time, use the chkconfig command:
[root@zion ~] # chkconfig mysqld on

To start, stop, and restart MySQL after boot time using the service commands.

[root@zion ~] # service mysqld start/stop/restart.
You can test whether the mysqld process is running with

[root@zion ~] # pgrep mysqld
MySQL listens on all your interfaces for database queries from remote MySQL clients. You can see this using netstat -an. Your server will be seen to be listening on IP address 0.0.0.0 (all) on TCP port 3306.

root@zion ~] # netstat -lpn | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1611/mysqld
unix 2 [ ACC ] STREAM LISTENING 5474 1611/mysqld /var/lib/mysql/mysql.sock
[root@zion ~] #

The Configuration File

The /etc/my.cnf file is the main MySQL configuration file

Location of MySQL Databases

Databases are usually located in a subdirectory of the /var/lib/mysql/ directory. ie, If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.

Creating a MySQL "root" Account

1. Make sure MySQL is started. 2. Use the mysqladmin command to set the MySQL root password.

[root@zion ~] # mysqladmin -u root password new-password

Accessing MySQL

You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p, which tells MySQL to prompt for a password.

[root@zion ~] # mysql -u root -p
Enter password:
[root@zion ~] #
Creating and Deleting MySQL Databases

Create database data:

mysql> create database data;
Query OK, 1 row affected (0.00 sec)
mysql>
Delete Database data:

mysql> drop database data;
Query OK, 0 rows affected (0.00 sec)
mysql>
Creating User

We create a user using :

mysql> create user rekha@localhost identified by "rekha";
Query OK, 0 rows affected (0.00 sec)
mysql>
Granting Privileges to Users

We have to create a MySQL username and password with privileges to access the database.

sql> grant all privileges on database.* to username@"servername" identified by 'password';
The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.

sql> flush privileges;

Running MySQL Scripts To Create Data Tables

We can run the script by logging into MySQL as the MySQL root user and automatically importing all the script file's commands with a ' desc login1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | UNI | NULL | |
| age | int(11) | YES | | NULL | |
| phone | varchar(15) | YES | | NULL | |
| s*x | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
mysql>
6. Viewing The Contents Of A Table

mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | hello |
+------+-------+
1 row in set (0.07 sec)
mysql> [/code]
You can specify range using : SELECT * FROM `your_table` LIMIT 0, 10

7. Delete a table

mysql> drop table 'table_name';
Query OK, 0 rows affected (0.08 sec)

mysql>

12/03/2023

MySQL Installation
Jump to navigationJump to search
MYSQL

MYSQL is the world's most popular open source database. MySQL AB is the sole owner of the MySQL server source code.

Installing MySQL
We have to install the MySQL server and MySQL client RPMs on the server. The client RPM gives you the ability to communicate with the server,

There are a number of supporting RPMs that may be needed, so the yum utility may be the best RPM installation method to use.
You can download the source rpm from

12/03/2023

Repairing tables using MYISAMCHK
Jump to navigationJump to search
When it comes to repairing and checking tables, MySQL offers two options:

The MySQL distribution ships with a tool called "myisamchk," designed specifically to check and repair MyISAM tables (the default table

type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair

corrupted tables. The myisamchk tool is invoked from the command line.

In addition to the tool above, MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE, REPAIR TABLE, and

OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client.

Most of the time, it's preferable to use myisamchk because it is significantly faster than using SQL commands.

Checking tables

If you're having trouble accessing a table, first try checking it for errors. To check a table, shut down the server and type myisamchk

at the command prompt, followed by the table's file name, as shown below:

$ myisamchk /usr/local/mysql/data/db2/payroll.myi
Checking MyISAM file: /usr/local/mysql/data/db2/payroll.MYI
Data records: 1153 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links
Use the complete path to the table file instead of just the table name. Remember to include the file extension as well

Repairing tables

The first—and most important—thing you can do before repairing a corrupted table is to back it up. Never try running a repair operation

on a table without backing it up; if you fail, your data might be in worse shape than originally and may even become unsalvageable.

Backing up the original table file allows you the option of trying a different technique if your first attempt doesn't work.

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users

accessing or otherwise manipulating the file during the repair operation.

Once the table file is backed up and the server down, you can run myisamchk with the --recover option, as shown below:

$ myisamchk -r /usr/local/mysql/data/db2/payroll.myi
- recovering (with sort) MyISAM-table '/usr/local/mysql/data/db2/payroll.MYI'Data records: 1153
- Fixing index 1

The --recover or -r option reconstructs the MySQL table index file after weeding the corrupted table of invalid or previously deleted

records that could be causing a problem.

In the unlikely event that the -r option fails, revert to the original table file and try the -o or --safe-recover option. This is

slower, because MySQL needs to scan through the records in the data file one by one and them restore the index, but it can sometimes

work better than a regular repair operation.

$ myisamchk -o /usr/local/mysql/data/db2/payroll.myi
- recovering (with keycache) MyISAM-table '/usr/local/mysql/data/db2/payroll.MYI'
Data records: 4

Now if you get an error like the following,

myisamchk error: Not enough memory for blob at 15946608 (need 182026713)
This occurs because the index entry indicates that the invalid field size blob is greater than that specified in brackets. The fix was

fairly simple, just add the option -max-record-length=0 .

$ myisamchk -max-record-length=0 -r /usr/local/mysql/data/db2/payroll.myi
In this case the value of - max-record-length = 0 can not define any limitations on field size blob .

Navigation menu
ManjushaTalkPreferencesWatchlistContributionsLog outPageDiscussionReadEditView historyWatch
More
Search
Search AdMod Internal Wiki
Main page
Recent changes
Random page
Help about MediaWiki
Tools
What links here
Related changes
Upload file
Special pages
Printable version
Permanent link
Page information
This page was last edited on 14 November 2010, at 10:10.

17/11/2022

19/01/2021

Vrealize 8 .1 cma 2v0 3120

29/12/2020

30/07/2020

https://www.roneyclubs.com/account/login?invite_key=38725838

Roney Shop,a shop from india.

14/07/2020

03/07/2020

Vra/vro. Program. Javascript custom workflow

23/06/2020

Vrealize 7.6. Now latest version vrealize 8

07/04/2020

A standard switch provides connections for VMs to communicate with one another whether they are on the same host or on different hosts.

07/04/2020

Standard switches can be created using any vsphere client

17/02/2020

https://docs.vmware.com/

10/02/2020

VSPHERE 6.7 latest version esxi